Copy data from multiple sheets to single sheet

[vmohan1978]

All,

I have the following 5 sheets in a file
WksOutput,WksData,WksBangalore,WksDelhi,WksMumbai.

I want to copy data from WksBangalore,WksDelhi,WksMumbai to WksData sheet continuously.

I am using the following data

Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets(3).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=WksData.Range("A1")
For J = 4 To Sheets.Count
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=WksData.Range("A65536").End(xlUp)(2)
Next
End Sub

 

[vmohan1978]

I am not getting the output please do the needful.

 

[AlanSidman]

It would be much easier if you described in detail as if we were standing in line to get coffee what you are trying to do, instead of me trying to read your mind and understand what you want your code to do since it is not doing what you want it to do. Posting a copy of your workbook, sanitized for confidential data would be very helpful.

 

[vmohan1978]

I need a code to copy data from sheets WksBangalore,WksDelhi,WksMumbai and paste in the sheet WksData.
Find attached the excel file.

I wanted output as shown in the WksData sheet.
At present I copy and pasted in the WksData sheet.

 

[rahulshewale1]

hii @vmohan1978,

Please find the attached solution using Power Query.

Download Power Query from Microsoft site.

Go thorough Below Step:
Step 1:Convert Data into Table(Bangalore ,Delhi,Mumbai)
Step 2 :Go to Power Query Tab >From Table/Range >>Close & load Connection Only>>Load to Only Create Connection (follow this step for each Sheet)
Step 3 :Go to Power Query Tab >Append >>Select Three Or more tables > Add all region sheet >> Ok >>Close &Load


Regard
Rahul shewale

 

[vmohan1978]

Rahul,

Thank you very much, it's working perfectly.
Is it possible to insert the Refresh button in the sheet "Wksoutput", I want to hide the sheet "Wks data via power query" so that the team will update the 3table(Bangalore,Delhi, and Mumbai) only.

 

[rahulshewale1]

hii @vmohan1978,


See if is ok ?

Regard
Rahul shewale

 

[vmohan1978]

Hi,

Rahul Thank you very much, its working perfectly.

I need little more clarification I had copied and pasted the code in other file and its showing the error.
I am sorry can you please explain it. where I am doing mistake.

 

[rahulshewale1]

hii,

see if is ok ?

Regard
Rahul shewale

Public Sub UpdatePowerQueries()
' Macro to update my Power Query script(s)

Dim lTest As Long, cn As WorkbookConnection
On Error Resume Next
For Each cn In ThisWorkbook.Connections
lTest = InStr(1, cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1", vbTextCompare)
If Err.Number <> 0 Then
Err.Clear
Exit For
End If
If lTest > 0 Then cn.Refresh
Next cn

End Sub

 

[vmohan1978]

Rahul,

Sorry for the late response.

Thank you very much , it worked perfectly.