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