Copy dynamic range between workbooks

[elieprolb]

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/yztypvbx7b4o7w8/Capture.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.
Also Cross-Posted in the following forums:
Macro from 1st workbook:

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-E.NAJEM-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:

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-E.NAJEM-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...

I asked this question on excelforum.com with no luck so far:
Copy dynamic range between workbooks

Also cross-posted in the following forums:
http://www.excelguru.ca/forums/showthread.php?7334-Copy-dynamic-range-between-workbooks
http://www.msofficeforums.com/excel-programming/34106-copy-dynamic-range-between-workbooks.html

 

[elieprolb]

Link to the sheet:
https://www.dropbox.com/s/5ve0omjt3vhkldg/Sheet.xlsm?dl=0

I added the column value related to Today() from row#4 in the upper left corner.

All three workbooks are similar to the one attached in this link (one has different sheet name inside though).

Thanks.

 

[elieprolb]

Solved by NoS @ excelguru.ca


Copy dynamic range between workbooks - Page 2