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).
Adjust the below code to fit your requirements:
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)
Thanks to TrowaD
for this tip.
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