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

June 2017




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.

Related


Published by deri58. Latest update on January 22, 2013 at 09:24 AM by deri58.
This document, titled "Excel - Copy data from a range of sheets to a master sheet," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).