Multiple Dynamic Range Copy/Paste From Multiple Sheets to Master Sheet

[MasterExcel]

Hi Everyone,

I'm struggling to modify the code that I have to copy an extra dynamic range to the master sheet.

I have a master task sheet with code to copy a dynamic range (A2:E) from all sheets with the word "Data" at the end of the sheet name.

I'd also like to copy the data from the range G2:G from the same sheets but I can't seem to get the syntax right...

Any help would be gratefully received!

My code is below:

Sub RetrieveData()
   Dim ws As Worksheet
      
   With Sheets("Master Tasks")
      If .Range("A2") <> "" Then .Range("A2:E" & .Range("A" & Rows.Count).End(xlUp).Row).ClearContents
         For Each ws In Worksheets
         If ws.Name Like "*Data" Then
          ws.Range("A2:E" & ws.Range("A" & Rows.Count).End(xlUp).Row).Copy .Range("A" & Rows.Count).End(xlUp).Offset(1)
        End If
      Next ws
   End With
End Sub

 

[MasterExcel]

With some more research, I'm now wondering if it would be possible to hide column F and copy the whole lot using .SpecialCells(xlVisible). Can anybody shed any light on whether this would work or not?

Thanks again!

 

[Joe4]

Would it be possible to post a sample of that one of these "Data" sheets looks like, and the "Master Tasks" sheet you are pasting to?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[MasterExcel]

Back to Work Planning Sample.xlsm
	A	B	C	D	E	F	G	H	I
1	Project	Task	Manager	Start Date	Duration	End Date	Days Completed	Progress	Additional Task Detail
2						#NUM!		#DIV/0!
3
Master Tasks

Cell Formulas
Range	Formula
F2	F2	=WORKDAY.INTL('Master Tasks'!$D2-1,'Master Tasks'!$E2,1)
H2	H2	=[@[Days Completed]]/[@Duration]

 

[MasterExcel]

Back to Work Planning Sample.xlsm
	A	B	C	D	E	F	G	H	I
1	Project	Task	Manager	Start Date	Duration	End Date	Days Completed	Progress	Additional Task Detail
2	Desk Setup	remove unecessary desk	David R	24/05/2021	1	24/05/2021	1	100%
3	Video Call Booths	Source soundproofing	Janice	25/05/2021	1	25/05/2021	0	0%
Office Configuration Data

Cell Formulas
Range	Formula
F2:F3	F2	=WORKDAY.INTL('Office Configuration Data'!$D2-1,'Office Configuration Data'!$E2,1)
H2:H3	H2	=[@[Days Completed]]/[@Duration]

 

[MasterExcel]

Would it be possible to post a sample of that one of these "Data" sheets looks like, and the "Master Tasks" sheet you are pasting to?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

No problem Joe! There are 4 data sheets that will all feed into the Master Tasklist, from which I have created a pivot table with Gantt chart to track these tasks over time.

Thanks again for looking at my problem!

 

[Joe4]

Try this:

Sub RetrieveData()
    
    Dim ws As Worksheet
    Dim nr As Long, lr As Long
      
    With Sheets("Master Tasks")
        If .Range("A2") <> "" Then .Range("A2:E" & .Range("A" & Rows.Count).End(xlUp).Row).ClearContents
        
        For Each ws In Worksheets
            If ws.Name Like "*Data" Then
                'Find next blank row on Task sheet
                nr = .Cells(Rows.Count, "A").End(xlUp).Row + 1
                'Find last row with data on Data sheet
                lr = ws.Range("A" & Rows.Count).End(xlUp).Row
                'Copy over columns A:E
                ws.Range("A2:E" & lr).Copy .Range("A" & nr)
                'Copy over column G
                ws.Range("G2:G" & lr).Copy .Range("G" & nr)
            End If
        Next ws
        
    End With
    
End Sub

 

[MasterExcel]

That works perfectly! Thanks for all your help!!!

 

[Joe4]

You are welcome.