Copy dynamic range between workbooks

elieprolb · #1 02-01-2017, 07:12 AM

Greetings,

I have three workbooks, containing a similar sheet.
I need to copy a certain range from two workbooks into the third one.

The sheet looks like a calendar, and I fill data in the corresponding cells.

Image link: https://www.dropbox.com/s/yztypvbx7b...pture.PNG?dl=0

I have a macro that copies the data from both workbooks, but the way I'm doing it, the 2nd workbook is copying above the data of the first.

The 1st workbook has data from beginning of month til Today().
The 2nd workbook has data starting from tomorrow.

Macro from 1st workbook:

Code:

Sub TRANSFER()

    Dim y As Workbook
    Dim x As Workbook
    Dim ws As Worksheet
    Dim sh As Worksheet
    Dim rng As Range
    
    ' Current workbook
    Set y = ActiveWorkbook
    
    Application.ScreenUpdating = 0
    
    ' Target workbook
    Set x = Workbooks.Open("Z:\20-Production-Follow up\BVL-CTC-Works Schedule-2017.xlsx")
    
    ' Current worksheet
    Set ws = y.Sheets("Schedule")
    
    ' Target worksheet
    Set sh = x.Sheets("Schedule EN")
    
    ' Copying projects list
    Set rng = ws.Range("A5:B400")
    rng.Copy
    x.Sheets("Schedule EN").Range("A5:B400").PasteSpecial xlValues
    Application.CutCopyMode = False
    
    ' Copying data
    Set rng = ws.Range("G5:AK400")
    rng.Copy
    x.Sheets("Schedule EN").Range("G5:AK400").PasteSpecial xlValues
    Application.CutCopyMode = False
    
    x.Save
    x.Close
    
End Sub

Macro from 2nd workbook:

Code:

Sub TRANSFER()

    Dim y As Workbook
    Dim x As Workbook
    Dim ws As Worksheet
    Dim sh As Worksheet
    Dim rng As Range
    
    ' Current workbook
    Set y = ActiveWorkbook
    
    Application.ScreenUpdating = 0
    
    ' Target workbook
    Set x = Workbooks.Open("Z:\20-Production-Follow up\BVL-CTC-Works Schedule-2017.xlsx")
    
    ' Current worksheet
    Set ws = y.Sheets("Schedule")
    
    ' Target worksheet
    Set sh = x.Sheets("Schedule EN")
       
    ' Copying data
    Set rng = ws.Range("G5:NR400")
    rng.Copy
    x.Sheets("Schedule EN").Range("G5:NR400").PasteSpecial xlValues
    Application.CutCopyMode = False
    
    x.Save
    x.Close
    
End Sub

You can see that Range("G5:NR400") from 2nd workbook is copying above Range("G5:AK400") from 1st workbook...

How can I alter the copy range code in a way so that:
- from the 1st workbook, the range starts from G5 and ends with the column related to Today()
- from the 2nd workbook, the range starts from Today() + 1 till NR400.

Thanks in advance...

This question is cross-posted in the following forums:
http://www.excelforum.com/showthread.php?t=1171963
https://www.mrexcel.com/forum/excel-...workbooks.html
http://www.excelguru.ca/forums/showt...ween-workbooks

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to copy workbooks from different worksheets into one new worksheet	abbani	Excel	3	12-12-2016 04:09 AM
Range COPY paste in workbooks sheets as variable	Fean	Excel Programming	3	06-07-2016 06:51 AM
Name a Range in a Word Document and then copy that range to the end of the doc w button click	DanNatCorning	Word VBA	1	04-29-2016 10:47 PM
Need Help with Code for Copy and Pasting between Workbooks	rsrasc	Excel Programming	4	03-10-2016 09:09 AM
Loop through folder of workbooks and copy range to other workbook	Snvlsfoal	Excel Programming	3	07-29-2011 05:55 AM