Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-01-2017, 07:12 AM
elieprolb elieprolb is offline Copy dynamic range between workbooks Windows 7 64bit Copy dynamic range between workbooks Office 2010 64bit
Novice
Copy dynamic range between workbooks
 
Join Date: Feb 2017
Posts: 5
elieprolb is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 02-01-2017, 09:28 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Copy dynamic range between workbooks Windows 7 64bit Copy dynamic range between workbooks Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #3  
Old 02-02-2017, 04:51 AM
elieprolb elieprolb is offline Copy dynamic range between workbooks Windows 7 64bit Copy dynamic range between workbooks Office 2010 64bit
Novice
Copy dynamic range between workbooks
 
Join Date: Feb 2017
Posts: 5
elieprolb is on a distinguished road
Default

Quote:
Originally Posted by Pecoflyer View Post
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).
Thank you for pointing this out and sorry for any inconvenience.

I updated my initial post to include all other links (and did same other there too).
Thanks.
Reply With Quote
  #4  
Old 02-02-2017, 04:59 AM
elieprolb elieprolb is offline Copy dynamic range between workbooks Windows 7 64bit Copy dynamic range between workbooks Office 2010 64bit
Novice
Copy dynamic range between workbooks
 
Join Date: Feb 2017
Posts: 5
elieprolb is on a distinguished road
Default

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.
Reply With Quote
  #5  
Old 02-06-2017, 12:40 AM
elieprolb elieprolb is offline Copy dynamic range between workbooks Windows 7 64bit Copy dynamic range between workbooks Office 2010 64bit
Novice
Copy dynamic range between workbooks
 
Join Date: Feb 2017
Posts: 5
elieprolb is on a distinguished road
Default

Solved by NoS @ excelguru.ca

http://www.excelguru.ca/forums/showt...0145#post30145
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy dynamic range between workbooks How to copy workbooks from different worksheets into one new worksheet abbani Excel 3 12-12-2016 04:09 AM
Copy dynamic range between workbooks Range COPY paste in workbooks sheets as variable Fean Excel Programming 3 06-07-2016 06:51 AM
Copy dynamic range between workbooks 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

Other Forums: Access Forums

All times are GMT -7. The time now is 02:37 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft