#1
|
|||
|
|||
Copy dynamic range between workbooks
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 Last edited by elieprolb; 02-02-2017 at 04:50 AM. |
#2
|
||||
|
||||
Please,do not crosspost your question on multiple forums without including links here to the other threads on other forums.
Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post. We are here to help so help us to help you! Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
Quote:
I updated my initial post to include all other links (and did same other there too). Thanks. |
#4
|
|||
|
|||
Link to the sheet:
https://www.dropbox.com/s/5ve0omjt3v...heet.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. |
#5
|
|||
|
|||
|
Thread Tools | |
Display Modes | |
|
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 |