Excel - Copy data from a range of sheets to a master sheet

Ask a question



Issue


I need to develop a macro that will collect data from a range of sheets in a workbook.

The macro needs to:
  • Copy data for a range of sheets after the sheet called "All Deadlines" and before the sheet called "Template". So all sheets falling between sheet "All Deadlines" and sheet "Template".
  • I need all rows which have data in them from Row 14 onwards for sheets in the range, but for the macro only to copy the rows which include data
  • Once launch, the macro should copy the data to the sheet called "All Deadlines", pasting to Row 3 for the first time and pasting the data from each subsequent sheet below the last previous entry.
  • When run, the macro should delete all data from Row 3 and below on the sheet "All Deadlines" (data collected from previous runs of the macro).

Solution


Adjust the below code to fit your requirements:

Sub MoveData()
Dim ws As Worksheet
Dim lRow, dRow As Integer

Sheets("All Deadlines").Rows("3:" & Range("A" & Rows.Count).End(xlUp).Row).ClearContents

For Each ws In Sheets
    If ws.Name = "Create New Project" _
    Or ws.Name = "Project Dashboard" _
    Or ws.Name = "All Deadlines" _
    Or ws.Name = "Template" Then GoTo Nextws
    
    dRow = Sheets("All Deadlines").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
    lRow = Sheets(ws.Name).Range("A" & Rows.Count).End(xlUp).Row
    Sheets(ws.Name).Rows("14:" & lRow).Copy Sheets("All Deadlines").Range("A" & dRow)
Nextws:
Next ws

End Sub


Thanks to TrowaD for this tip.
Jean-François Pillou

CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Learn more about the CCM team