Excel Macro to combine worksheets

pbajeff29

New Member
Joined
Aug 16, 2010
Messages
20
Office Version
  1. 365
I am looking for a macro that will step through each sheet and add all rows with data to the first sheet. It will need to look from row 2 down and then paste to the first empty row at the bottom of the list in page 1.

Example:
Sheet 1 - Results
Sheet 2 - AM
Sheet 3 - PM

Columns (A-P) match in all three row. And each sheet will be variable number of rows and changing daily.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Perhaps this ?

VBA Code:
Option Explicit

Sub CpyAllSheets()

Dim WS1, WScur As Worksheet
Dim i As Integer, lrow1 As Long, lrow As Long

Set WS1 = Sheets("Sheet1")

Application.ScreenUpdating = False

For i = 2 To ActiveWorkbook.Worksheets.Count
        lrow1 = WS1.Cells(Rows.Count, 2).End(xlUp).Row + 1
        Set WScur = Sheets(i)
        lrow = WScur.Cells(Rows.Count, 1).End(xlUp).Row
        WScur.Range("a2:p" & lrow).Copy WS1.Range("a" & lrow1)
    
Next i

Application.ScreenUpdating = True
End Sub
 
Upvote 0
Perhaps this ?

VBA Code:
Option Explicit

Sub CpyAllSheets()

Dim WS1, WScur As Worksheet
Dim i As Integer, lrow1 As Long, lrow As Long

Set WS1 = Sheets("Sheet1")

Application.ScreenUpdating = False

For i = 2 To ActiveWorkbook.Worksheets.Count
        lrow1 = WS1.Cells(Rows.Count, 2).End(xlUp).Row + 1
        Set WScur = Sheets(i)
        lrow = WScur.Cells(Rows.Count, 1).End(xlUp).Row
        WScur.Range("a2:p" & lrow).Copy WS1.Range("a" & lrow1)
   
Next i

Application.ScreenUpdating = True
End Sub
I got this part of the code accomplished using power query...now I need a code that refreshes the SharePoint query (which I have) but ALSO updates the pivot that is based on the append from the power query. I have thrown everything at it and cannot get it to update in a single macro run.
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top