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

MasterExcel

New Member
Joined
May 24, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

MasterExcel

New Member
Joined
May 24, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,989
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 24, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Back to Work Planning Sample.xlsm
ABCDEFGHI
1ProjectTaskManagerStart DateDurationEnd DateDays CompletedProgressAdditional Task Detail
2#NUM!#DIV/0!
3
Master Tasks
Cell Formulas
RangeFormula
F2F2=WORKDAY.INTL('Master Tasks'!$D2-1,'Master Tasks'!$E2,1)
H2H2=[@[Days Completed]]/[@Duration]
 

MasterExcel

New Member
Joined
May 24, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Back to Work Planning Sample.xlsm
ABCDEFGHI
1ProjectTaskManagerStart DateDurationEnd DateDays CompletedProgressAdditional Task Detail
2Desk Setupremove unecessary deskDavid R24/05/2021124/05/20211100%
3Video Call BoothsSource soundproofingJanice25/05/2021125/05/202100%
Office Configuration Data
Cell Formulas
RangeFormula
F2:F3F2=WORKDAY.INTL('Office Configuration Data'!$D2-1,'Office Configuration Data'!$E2,1)
H2:H3H2=[@[Days Completed]]/[@Duration]
 

MasterExcel

New Member
Joined
May 24, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,989
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Try this:
VBA Code:
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
 
Solution

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,989
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Forum statistics

Threads
1,141,315
Messages
5,705,693
Members
421,406
Latest member
kluna90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top