Copy paste from one Excel workbook to another with same sheet names and update dates in range

[fishmonger]

I am trying to copy paste info from a "Data" Sheet to a "Master" Sheet. The "Data" and "Master" Sheet contain some sheet names in common (Master, Scenario A, Scenario B, Scenario C , Scenario D, Scenario E and Scenario F) but also have some other sheets not common in both. I would only want to copy paste the data in the common sheet names.
After copy pasting the data I would like to update the dates Starting from Z5: Z5 and AA5 should be Jan 2024 , AB5 and AC5 should be Feb 2024, AD5 and AE5 should be March 2024 and the date rename should continue until a defined month (eg I would like it to update in this pattern until Jun 2024 only and maybe another time I would want it to update in this pattern until August 2024 only)

I have the below VBA code sample for the copy pasting part but could not figure out the date updating part. I manually update the dates outside of the macro. Is there a better way to ac this copy paste part & also incorporate the date updating? Thank you.

Workbooks.Open Filename:=ThisWorkbook.Path & "\Data*"
Dim Data As Workbook
Set Data = ActiveWorkbook
Workbooks.Open Filename:=ThisWorkbook.Path & "\Summary*"
Dim Summary As Workbook
Set Summary = ActiveWorkbook

Data.Activate
Sheets("Master").Select
Columns("A:Z").Select
Selection.Copy
Summary.Activate
Sheets("Master").Select
Range("Z1").Select
ActiveSheet.Paste

Data.Activate
Sheets("Scenario A").Select
Columns("A:Z").Select
Selection.Copy
Summary.Activate
Sheets("Scenario A").Select
Range("Z1").Select
ActiveSheet.Paste

Data.Activate
Sheets("Scenario B").Select
Columns("A:Z").Select
Selection.Copy
Summary.Activate
Sheets("Scenario B").Select
Range("Z1").Select
ActiveSheet.Paste

Data.Activate
Sheets("Scenario C").Select
Columns("A:Z").Select
Selection.Copy
Summary.Activate
Sheets("Scenario C").Select
Range("Z1").Select
ActiveSheet.Paste

 

[RobP]

Hi, I interpreted what you needed in this way. You can set your sheet names up as you want in "sheet_names" declaration, and also your end date.
Try it on a copy of your workbooks in the first instance to avoid any issue.

Workbooks.Open Filename:=ThisWorkbook.Path & "\Data*"
Dim Data As Workbook
Set Data = ActiveWorkbook
Workbooks.Open Filename:=ThisWorkbook.Path & "\Summary*"
Dim Summary As Workbook
Set Summary = ActiveWorkbook

Dim mnth As Date
Dim sheet_names As Variant
sheet_names = Array("Master", "Scenario A", "Scenario B", "Scenario C") ' add the sheet names interested in here ..
mnth = "01/01/24"

For x = 0 To UBound(sheet_names) 'array starts at zero, ends at last sheet defined in above declaration (in this case 3) (0 > 3 = 4 sheets)
    Data.Sheets(sheet_names(x)).Columns("a:z").Copy Summary.Sheets(sheet_names(x)).Range("Z1") 'copy the data on each sheet
    col = 26 'Col "Z"
    Do While mnth <= "01/12/24" ' set your end date here
        
        With Summary.Sheets(sheet_names(x))
            .Range(.Cells(5, col), .Cells(5, col + 1)).Value = MonthName(DatePart("m", mnth), True) & " " & DatePart("yyyy", mnth) 'uses "01/01/24" as a start reference
        End With
        mnth = DateAdd("m", 1, mnth) 'move date on by 1 month
        col = col + 2 'move columns by 2 for next date
    Loop
Next x


End Sub

Let me know if it works for you.

 

[fishmonger]

Hi, I interpreted what you needed in this way. You can set your sheet names up as you want in "sheet_names" declaration, and also your end date.
Try it on a copy of your workbooks in the first instance to avoid any issue.

Workbooks.Open Filename:=ThisWorkbook.Path & "\Data*"
Dim Data As Workbook
Set Data = ActiveWorkbook
Workbooks.Open Filename:=ThisWorkbook.Path & "\Summary*"
Dim Summary As Workbook
Set Summary = ActiveWorkbook

Dim mnth As Date
Dim sheet_names As Variant
sheet_names = Array("Master", "Scenario A", "Scenario B", "Scenario C") ' add the sheet names interested in here ..
mnth = "01/01/24"

For x = 0 To UBound(sheet_names) 'array starts at zero, ends at last sheet defined in above declaration (in this case 3) (0 > 3 = 4 sheets)
    Data.Sheets(sheet_names(x)).Columns("a:z").Copy Summary.Sheets(sheet_names(x)).Range("Z1") 'copy the data on each sheet
    col = 26 'Col "Z"
    Do While mnth <= "01/12/24" ' set your end date here
     
        With Summary.Sheets(sheet_names(x))
            .Range(.Cells(5, col), .Cells(5, col + 1)).Value = MonthName(DatePart("m", mnth), True) & " " & DatePart("yyyy", mnth) 'uses "01/01/24" as a start reference
        End With
        mnth = DateAdd("m", 1, mnth) 'move date on by 1 month
        col = col + 2 'move columns by 2 for next date
    Loop
Next x


End Sub

Let me know if it works for you.

Wow. Thank you so much Rob. This is very heplful.
-For the end date - is there a way to get a pop up that asks what end date to use instead of setting it within the coding? I have tried using "Input box" but could not get it working.
-Also based on the end date - is there a way to use end date to set the end Column for the copy and paste. Right now it is copying Columns A to Z but for example if want only until end date of July 2024 it should pick Colum(A:O) of if desired end date is Feb 2024 it should use columns (A:E)
-For the date inserting part - how can i get it to put the actual date not just date part? So in the Column Z5 how to get the full 01/01/2024 instead of Jan 2024?

 

[RobP]

Hi,
I think I've interpreted what you want - but you threw me with the end date setting the column comment, as you appear to always want one more column than the actual months (if I read correctly).
eg. end date of FEB for me would mean A:D (or 4 columns, 2 for each date), but you ask for A:E

So my line here :

Data.Sheets(sheet_names(x)).Range(Columns(1), Columns((mnth * 2)+1)).Copy Summary.Sheets(sheet_names(x)).Range("Z1")

has a "+1" in the middle to allow for that.

cheers
Rob

Sub move_data()
Workbooks.Open Filename:=ThisWorkbook.Path & "\Data*"
Dim Data As Workbook
Set Data = ActiveWorkbook
Workbooks.Open Filename:=ThisWorkbook.Path & "\Summary*"
Dim Summary As Workbook
Set Summary = ActiveWorkbook

Dim emnth, smnth As Date
Dim sheet_names As Variant
sheet_names = Array("Master", "Scenario A", "Scenario B", "Scenario C") ' add the sheet names interested in here ..

smnth = "01/01/24" 'start date
emnth = InputBox("End Date : dd/mm/yy", "Please Provide End Date Requirement", "dd/mm/yy") ' end date
mnth = DatePart("m", emnth, True) ' month number of end date

For x = 0 To UBound(sheet_names) 'array starts at zero, ends at last sheet defined in above declaration (in this case 3) (0 > 3 = 4 sheets)
    Data.Sheets(sheet_names(x)).Range(Columns(1), Columns((mnth * 2)+1)).Copy Summary.Sheets(sheet_names(x)).Range("Z1") 'copy the data on each sheet
    col = 26 'Col "Z" starting point for pasting dates
    Do While smnth <= emnth
      
        With Summary.Sheets(sheet_names(x))
            .Range(.Cells(5, col), .Cells(5, col + 1)).Value = smnth 'MonthName(DatePart("m", smnth), True) & " " & DatePart("yyyy", smnth) 'uses "01/01/24" as a start reference
        End With
        smnth = DateAdd("m", 1, smnth) 'move date on by 1 month
        col = col + 2 'move columns by 2 for next date
    Loop
Next x


End Sub

 

[fishmonger]

Hi Rob,

Thank you again so much for helping me . I went back to using the previous code you had graciously provided because i could not get the new code to work.
However the previous code is not looping. It will work only on the first sheet.
I think new code doesnt work because of the additional step i requested of having the range pasted based on a defined date in pop up - it seems the updating date part would need to come before the copy pasting range part.

I have thought about this more and attached screenshot to explain better what I am trying to achieve - from my first description I framed it as pasting the unmodify data into a Summary workbook in cell Z1 and then doing modifications to the data in the summary workbook.
The screenshot is based on cleaning up the raw data first and then pasting into Summary workbook once it is in a good format. The good format data should sbe copy paste staring in Column Z1 of the Summary workbook

Hi,
I think I've interpreted what you want - but you threw me with the end date setting the column comment, as you appear to always want one more column than the actual months (if I read correctly).
eg. end date of FEB for me would mean A:D (or 4 columns, 2 for each date), but you ask for A:E

So my line here :

Data.Sheets(sheet_names(x)).Range(Columns(1), Columns((mnth * 2)+1)).Copy Summary.Sheets(sheet_names(x)).Range("Z1")

has a "+1" in the middle to allow for that.

cheers
Rob

Sub move_data()
Workbooks.Open Filename:=ThisWorkbook.Path & "\Data*"
Dim Data As Workbook
Set Data = ActiveWorkbook
Workbooks.Open Filename:=ThisWorkbook.Path & "\Summary*"
Dim Summary As Workbook
Set Summary = ActiveWorkbook

Dim emnth, smnth As Date
Dim sheet_names As Variant
sheet_names = Array("Master", "Scenario A", "Scenario B", "Scenario C") ' add the sheet names interested in here ..

smnth = "01/01/24" 'start date
emnth = InputBox("End Date : dd/mm/yy", "Please Provide End Date Requirement", "dd/mm/yy") ' end date
mnth = DatePart("m", emnth, True) ' month number of end date

For x = 0 To UBound(sheet_names) 'array starts at zero, ends at last sheet defined in above declaration (in this case 3) (0 > 3 = 4 sheets)
    Data.Sheets(sheet_names(x)).Range(Columns(1), Columns((mnth * 2)+1)).Copy Summary.Sheets(sheet_names(x)).Range("Z1") 'copy the data on each sheet
    col = 26 'Col "Z" starting point for pasting dates
    Do While smnth <= emnth
    
        With Summary.Sheets(sheet_names(x))
            .Range(.Cells(5, col), .Cells(5, col + 1)).Value = smnth 'MonthName(DatePart("m", smnth), True) & " " & DatePart("yyyy", smnth) 'uses "01/01/24" as a start reference
        End With
        smnth = DateAdd("m", 1, smnth) 'move date on by 1 month
        col = col + 2 'move columns by 2 for next date
    Loop
Next x


End Sub

Hi,
I think I've interpreted what you want - but you threw me with the end date setting the column comment, as you appear to always want one more column than the actual months (if I read correctly).
eg. end date of FEB for me would mean A:D (or 4 columns, 2 for each date), but you ask for A:E

So my line here :

Data.Sheets(sheet_names(x)).Range(Columns(1), Columns((mnth * 2)+1)).Copy Summary.Sheets(sheet_names(x)).Range("Z1")

has a "+1" in the middle to allow for that.

cheers
Rob

Sub move_data()
Workbooks.Open Filename:=ThisWorkbook.Path & "\Data*"
Dim Data As Workbook
Set Data = ActiveWorkbook
Workbooks.Open Filename:=ThisWorkbook.Path & "\Summary*"
Dim Summary As Workbook
Set Summary = ActiveWorkbook

Dim emnth, smnth As Date
Dim sheet_names As Variant
sheet_names = Array("Master", "Scenario A", "Scenario B", "Scenario C") ' add the sheet names interested in here ..

smnth = "01/01/24" 'start date
emnth = InputBox("End Date : dd/mm/yy", "Please Provide End Date Requirement", "dd/mm/yy") ' end date
mnth = DatePart("m", emnth, True) ' month number of end date

For x = 0 To UBound(sheet_names) 'array starts at zero, ends at last sheet defined in above declaration (in this case 3) (0 > 3 = 4 sheets)
    Data.Sheets(sheet_names(x)).Range(Columns(1), Columns((mnth * 2)+1)).Copy Summary.Sheets(sheet_names(x)).Range("Z1") 'copy the data on each sheet
    col = 26 'Col "Z" starting point for pasting dates
    Do While smnth <= emnth
     
        With Summary.Sheets(sheet_names(x))
            .Range(.Cells(5, col), .Cells(5, col + 1)).Value = smnth 'MonthName(DatePart("m", smnth), True) & " " & DatePart("yyyy", smnth) 'uses "01/01/24" as a start reference
        End With
        smnth = DateAdd("m", 1, smnth) 'move date on by 1 month
        col = col + 2 'move columns by 2 for next date
    Loop
Next x


End Sub

 

[RobP]

Hi, sorry, I think I needed to reset the month data each time, so will put those inside the sheet loop :

Sub move_data()
Workbooks.Open Filename:=ThisWorkbook.Path & "\Data*"
Dim Data As Workbook
Set Data = ActiveWorkbook
Workbooks.Open Filename:=ThisWorkbook.Path & "\Summary*"
Dim Summary As Workbook
Set Summary = ActiveWorkbook

Dim emnth, smnth As Date
Dim sheet_names As Variant
sheet_names = Array("Master", "Scenario A", "Scenario B", "Scenario C") ' add the sheet names interested in here ..

emnth = InputBox("End Date : dd/mm/yy", "Please Provide End Date Requirement", "dd/mm/yy") ' end date
mnth = DatePart("m", emnth, True) ' month number of end date

For x = 0 To UBound(sheet_names) 'array starts at zero, ends at last sheet defined in above declaration (in this case 3) (0 > 3 = 4 sheets)

    smnth = "01/01/24" 'start date
    
    Data.Sheets(sheet_names(x)).Range(Columns(1), Columns((mnth * 2)+1)).Copy Summary.Sheets(sheet_names(x)).Range("Z1") 'copy the data on each sheet
    col = 26 'Col "Z" starting point for pasting dates
    Do While smnth <= emnth
      
        With Summary.Sheets(sheet_names(x))
            .Range(.Cells(5, col), .Cells(5, col + 1)).Value = smnth 'MonthName(DatePart("m", smnth), True) & " " & DatePart("yyyy", smnth) 'uses "01/01/24" as a start reference
        End With
        smnth = DateAdd("m", 1, smnth) 'move date on by 1 month
        col = col + 2 'move columns by 2 for next date
    Loop
Next x


End Sub

 

[fishmonger]

Hi, sorry, I think I needed to reset the month data each time, so will put those inside the sheet loop :

Sub move_data()
Workbooks.Open Filename:=ThisWorkbook.Path & "\Data*"
Dim Data As Workbook
Set Data = ActiveWorkbook
Workbooks.Open Filename:=ThisWorkbook.Path & "\Summary*"
Dim Summary As Workbook
Set Summary = ActiveWorkbook

Dim emnth, smnth As Date
Dim sheet_names As Variant
sheet_names = Array("Master", "Scenario A", "Scenario B", "Scenario C") ' add the sheet names interested in here ..

emnth = InputBox("End Date : dd/mm/yy", "Please Provide End Date Requirement", "dd/mm/yy") ' end date
mnth = DatePart("m", emnth, True) ' month number of end date

For x = 0 To UBound(sheet_names) 'array starts at zero, ends at last sheet defined in above declaration (in this case 3) (0 > 3 = 4 sheets)

    smnth = "01/01/24" 'start date
   
    Data.Sheets(sheet_names(x)).Range(Columns(1), Columns((mnth * 2)+1)).Copy Summary.Sheets(sheet_names(x)).Range("Z1") 'copy the data on each sheet
    col = 26 'Col "Z" starting point for pasting dates
    Do While smnth <= emnth
     
        With Summary.Sheets(sheet_names(x))
            .Range(.Cells(5, col), .Cells(5, col + 1)).Value = smnth 'MonthName(DatePart("m", smnth), True) & " " & DatePart("yyyy", smnth) 'uses "01/01/24" as a start reference
        End With
        smnth = DateAdd("m", 1, smnth) 'move date on by 1 month
        col = col + 2 'move columns by 2 for next date
    Loop
Next x


End Sub

Thanks Rob!

 

[RobP]

Thanks for feedback, glad it worked for you.

cheers
Rob

 

[Skiier89]

I am trying to copy paste info from a "Data" Sheet to a "Master" Sheet. The "Data" and "Master" Sheet contain some sheet names in common (Master, Scenario A, Scenario B, Scenario C , Scenario D, Scenario E and Scenario F) but also have some other sheets not common in both. I would only want to copy paste the data in the common sheet names.
After copy pasting the data I would like to update the dates Starting from Z5: Z5 and AA5 should be Jan 2024 , AB5 and AC5 should be Feb 2024, AD5 and AE5 should be March 2024 and the date rename should continue until a defined month (eg I would like it to update in this pattern until Jun 2024 only and maybe another time I would want it to update in this pattern until August 2024 only)

I have the below VBA code sample for the copy pasting part but could not figure out the date updating part. I manually update the dates outside of the macro. Is there a better way to ac this copy paste part & also incorporate the date updating? Thank you.

Workbooks.Open Filename:=ThisWorkbook.Path & "\Data*"
Dim Data As Workbook
Set Data = ActiveWorkbook
Workbooks.Open Filename:=ThisWorkbook.Path & "\Summary*"
Dim Summary As Workbook
Set Summary = ActiveWorkbook

Data.Activate
Sheets("Master").Select
Columns("A:Z").Select
Selection.Copy
Summary.Activate
Sheets("Master").Select
Range("Z1").Select
ActiveSheet.Paste

Data.Activate
Sheets("Scenario A").Select
Columns("A:Z").Select
Selection.Copy
Summary.Activate
Sheets("Scenario A").Select
Range("Z1").Select
ActiveSheet.Paste

Data.Activate
Sheets("Scenario B").Select
Columns("A:Z").Select
Selection.Copy
Summary.Activate
Sheets("Scenario B").Select
Range("Z1").Select
ActiveSheet.Paste

Data.Activate
Sheets("Scenario C").Select
Columns("A:Z").Select
Selection.Copy
Summary.Activate
Sheets("Scenario C").Select
Range("Z1").Select
ActiveSheet.Paste

I do not thing VBA is necessary. If you don't need to write code, why would you?

Solution:

• In the source workbook, identify the formulas and/or functions that refer to globally scoped named ranges.
• Within each worksheet, open the Name Manager and create a locally scoped named range that contains the globally scoped named range. You will reference the global name using the indirect function: For example, if the range “Colors” is a global range stored on Sheet 1, and “Colors” is used in a formula or function on Sheet 2, create a local name on Sheet 2 (let’s call it Colors2) defined as =indirect(“Colors”). Do this for all instances on all worksheets you desire to copy / move to other workbooks.
• Where global names are referenced, substitute the local name you defined. Confirm your formulas still function. Continuing the example, if on Sheet 2 there is a function or formula that refers to the range “Colors” directly, substitute “Colors2” to refer to that range indirectly. Instead of =vlookup([value],Colors,1,false), use =vlookup([value],Colors2,1,false). If you receive a #REF effort, go back and check the Name Manager for misspellings.
• Before moving any worksheet from your source workbook to other destination workbooks, you must remove all global names. In the example, even if Sheet 2 were copied to another workbook after completing step 3, all global names from the source workbook will be transferred to the destination, which will duplicate the ranges that already exist at the destination. I suggest copying or moving each worksheet to their own separate workbooks. Continuing with the example, after you have changed the references on Sheet 2 to “Colors2”, copy or move the sheet into a separate new workbook (let’s call it Workbook_Sheet2). Save this new workbook, open the Name Manager, sort by scope, and remove all names that are scoped to the source workbook. Depending on whether you save > close > reopen, or just save the workbook, the global ranges will appear with the scope “workbook” or show the file name of the source workbook from where it was copied.
• From Workbook_Sheet2, you will now be able to move or copy the desired worksheet to other destination workbooks. The formulas and functions will update automatically, as long as the range(s) at destination is(are) called exactly the same as they are at the source (i.e. “Colors”). There will be no external links back to the source workbook. The only ranges that will be added to the Name Manager at the destination are those that were locally scoped to the sheet you copied, so you will not have duplicates in the Name Manager. You will also note that, because there are no external links, Excel will not substitute #REF into the local ranges that are copied with the sheet. You will however see #REF when you copy the sheet to a new workbook (to remove global names) before you move it to the destination. Lastly, and perhaps what’s the nicest feature of this process, is that the parameters of the named ranges in the destination workbook are preserved. Meaning, if the range Colors in the source workbook was stored on Sheet 1 in cells A1:A5, and the range Colors in the destination workbook is stored on Sheet 10 in cells A10:A20, the copied worksheet will preserve the named range of Colors at the destination (Sheet 10 cells A10:A20).
• Explanation:
• It may not be a literal definition, but I think of indirect as a function that converts text into a reference; you feed it a text string, and Excel looks for an object (whether it’s a named range, table, graph, etc.) that matches the text.
• Using indirect to refer to global names means that when the formulas/functions are copied/moved from one workbook to another, you aren’t copying references - you’re copying text strings. When those text strings arrive at the destination, the indirect function will look for an object at the destination that matches the text. On either end of the process – meaning at both the source workbook and the destination workbook – the indirect function successfully finds a reference to an object. In between the source and destination, the reference is preserved as a text string.
• Disclaimer:
• Indirect is a volatile function, and by storing it in the name manager, it may result in slow workbook performance if that name is used repetitively. This may motivate you to reconsider your functions/formulas that are most efficient. There’s a volume of good practices for ways to write efficient formulas, as well as information on how, among similar formulas (i.e. lookup formulas), certain functions are more efficient than others (for example, xlookup tends to be more efficient than vlookup or xlookup). You may also wish to take it a step further, and remove the indirect functions once the sheet(s) are copied to the destination.
• I haven’t found any other responses for this question online. What inspired my approach to this was a post that said “do not fight Excel, but instead exploit what it has to offer.” Please let me know if this solution works or if you have any feedback.