Macro Help: Copy Data from a Range of Sheets to a Master Sheet

[Solved/Closed]
Report
Posts
2
Registration date
Thursday January 17, 2013
Status
Member
Last seen
January 17, 2013
-
 supermate -
Hi,

I'm looking for some help developing a macro to collect data from a range of sheets in a workbook, but not all sheets.

The macro needs to:

1. 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"

2. 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

3. When run, 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

4. 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)

If anyone is able to help I would greatly appreciate it!

7 replies

Posts
2819
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 25, 2021
486
Hi Supermate,

Just a few questions:

1. How many sheets are there? Are "All Deadlines" and "Template" the only sheets excluded? And how many sheets are there between "All Deadlines" and "Template"? and preferably there names.
This because I don't know how to loop through sheets between to specific sheets.

2. No questions.

3. Are rows 1 and 2 filled with data?

4. No questions.

Best regards,
Trowa
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
2
Registration date
Thursday January 17, 2013
Status
Member
Last seen
January 17, 2013
1
Hi Trowa and thanks for your reply. Here are the answers to your questions:

1. The number of sheets will vary, increasing over time. I have another macro to create a new sheet from the Template worksheet every time I get a new project. At the moment there are three sheets at the start titled "Create New Project" "Project Dashboard" and "All Deadlines". following all deadlines will come individual worksheets dedicated to each project and the worksheet "Template" is then located right at the end. The sheets between "All Deadlines" and "Template" are currently just a bunch of random project names I came up with to use while I'm building the workbook.

3. Rows 1 and 2 have column headers in them.

I hope this is helpful?

Matt
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
2819
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 25, 2021
486
Hi Matt,

Ok, make a copy of your file, paste the code and try it out. Let me know if it works ok or if you want some adjustments.

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

Best regards,
Trowa
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Bingo! Thanks Trowa - that's great. I copied in the code and it worked like magic!

:) Matt
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
2819
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 25, 2021
486
Great testing Matt, you are right.

This line is the problem:
Sheets("All Deadlines").Rows("3:" & Range("A" & Rows.Count).End(xlUp).Row).ClearContents

The line clears data from row 3 until the last row used. When there is no data the last row used is row 2. So Rows 2 and 3 will be cleared.

So first we need to determine if any rows need to be cleared. We do this by checking if there is any data in cell A3.

Replace the above line by:
If Range("A3") <> vbNullString Then
Sheets("All Deadlines").Rows("3:" & Range("A" & Rows.Count).End(xlUp).Row).ClearContents
End If

Or just copy the entire code:
Sub MoveData()
Dim ws As Worksheet
Dim lRow, dRow As Integer

If Range("A3") <> vbNullString Then
Sheets("All Deadlines").Rows("3:" & Range("A" & Rows.Count).End(xlUp).Row).ClearContents
End If

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

Best regards,
Trowa
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Just one more question: when I run the macro repeatedly it doesn't always behave the same. Sometimes it duplicates one of the copied rows and sometimes it overwrites one of the header rows. Any ideas why that might be?

edit: my last couple of runs have resulted in the number of rows of data increasing by 1 row per run of the macro and the extra row is a duplicate of one of the existing rows. Can you see anything in the macro that might be causing this?
Thanks Trowa - that looks like its working.

I had something strange happen the first couple of runs, but it might have been interacting with other macros since I had a handful that I was testing in the same workbook, but it appears to be working now.

Matt