Copy data between two workbooks

shn.ea

New Member
Joined
Dec 3, 2009
Messages
7
Hi, we are trying to build a script that will copy data from two worksheets in a workbook to the corresponding worksheets in another workbook. I am using Excel 2007 on Windows Vista.



The old workbooks have data sorted by date from 2007 to 2009 while the new workbooks have data from 2007 to 2011, and we have to copy the overlapping period - we added two more years. There are thousands of workbooks, and in the process of adding the additional years, we also made changes to the other sheets, one being a chart. All workbooks contain patient samples from hospitals. In this particular example we are looking to copy C7, O7, C8, Q8, C10, F16:AE18, F20:AE20, and F25:AE25.

The old workbooks have different names (i.e. MedRec-LTC_1_HospitalName_0909_OLD.xls) but the new workbooks have a generic name (i.e. MedRec-LTC_1_Generic_NEW.xls).

My initial idea was to hardcode the new workbook name and determine the active workbook name. I tried to only copy the first cell of interest first, but the code does not work:

Code:
Sub MedRecLTC2()
    
    Dim WbSource As Workbooks
    Dim WbDestination As Workbooks
    
    Set WbSource = ActiveWorkbook.Name
    Set WbDestination = Workbooks("MedRec-LTC_1_Generic_NEW.xls")
        
    Workbooks("WbSource").Sheets("Data Entry Sheet").Range("O7").Copy
    Workbooks("WbDestination").Sheets("Data Entrey Sheet").Range("O7").Paste
    
End Sub
I tried variations that gave limited success; when I changed the detection of the source, I got it to copy the right cell:

Code:
    Source = ActiveWorkbook.Name
        
    Workbooks(Source).Sheets("Data Entry Sheet").Range("O7").Copy
But code did not work for the next step... I patched the code from various bits and pieces of info from various forums, so that makes me think I did not understand the right combination of steps to take; I am not viewing this in the right manner.

Any suggestions or direction?
 
Last edited:

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Jobowo

Board Regular
Joined
Oct 21, 2009
Messages
242
The syntax for moving data from one range to another is simply to define the source and destination and equate them. The syntax below assumes you are moving values (strictly speaking, each side should have .value at the end but you can get away without it). If you have formula for generating those values and the formula still works in wbDestination, then you do need to add .formula to both sides.


Code:
Sub MedRecLTC2()
    
    Dim WbSource As Workbooks
    Dim WbDestination As Workbooks
    
    Set WbSource = ActiveWorkbook
    Set WbDestination = Workbooks("MedRec-LTC_1_Generic_NEW.xls")
        
    WbDestination.Sheets("Data Entrey Sheet").Range("O7") = 
         WbSource.Sheets("Data Entry Sheet").Range("O7")

    
End Sub
Now, if you want to copy a series of ranges then you can put those strings in an array and loop through the array:
Code:
Sub MedRecLTC2()
    Dim Targets As Variant
    Targets = Array("A10", "B99", "C3:E75")
    
    
    Dim WbSource As Workbooks
    Dim WbDestination As Workbooks
    
    Set WbSource = ActiveWorkbook
    Set WbDestination = Workbooks("MedRec-LTC_1_Generic_NEW.xls")
    For i = 1 To 3
        With WbDestination.Sheets("Data Entrey Sheet")
           .Range(Targets(i)) = WbSource.Sheets("Data Entry Sheet").Range(Targets(i))
        End With
    Next
End Sub

Caution! This is "illustrative" code; I haven't checked it thoroughly!
 
Last edited:

shn.ea

New Member
Joined
Dec 3, 2009
Messages
7

Jobowo

Board Regular
Joined
Oct 21, 2009
Messages
242
Sorry, should have noticed: that would be (singular)

Dim WbSource As Workbook
Dim WbDestination As Workbook
</pre>
 

shn.ea

New Member
Joined
Dec 3, 2009
Messages
7

ADVERTISEMENT

Now I'm running in the same issue I had initially (the 2nd part of my original message) surrounding "ActiveWorkbook.Name" and assigning Workbooks("...")
 

sthrncaliguy

Board Regular
Joined
Jul 28, 2009
Messages
213
Hi!

Try this code....

Code:
Sub MedRecLTC2()
 
    Set WbSource = ThisWorkbook
    Set WbDestination = Workbooks.Open("C:/windows/mybook.xls")
 
    WbSource.Sheets("Data Entry Sheet").Range("A1:Z1000").Copy wbDestination.Sheets("Data Entry Sheet").Range("A1")
 
    wbDestination.Close True
 
End Sub

This code will open the destination file, copy the data across, close and save the destination workbook. You can add as many copy/paste lines as needed for your data ranges.
 

shn.ea

New Member
Joined
Dec 3, 2009
Messages
7

ADVERTISEMENT

My source and destination files are in the same directory so I tried the follwing to create a relative path to the destination file

Code:
    Set WbDestination = ThisWorkbook.Path & "\MedRec-LTC_1_Generic_NEW.xls"

But it gives me a compilation error with the "&"
 

Jobowo

Board Regular
Joined
Oct 21, 2009
Messages
242
You are using a variety of methods here and I'd suggest homing in on the one and solving the problems with that one.

Here's what should work:

You have the source workbook open so stick to:

set wbSource = thisworkbook (assuming it is thisworkbook)

Now you need to open the destination workbook, and assuming the destination is in the same directory path as the destination workbook, you can make sure of this by:

chDir(thisworkbook.path) ' Assumes that thisworkbook has been saved

Now you can
DestinationNm = "MedRec-LTC_1_Generic_NEW.xls"
workbooks.Open(DestinationNm)
Set wbDestination = workbooks(DestinationNm)

OK we're all set up. Now you can do your loop.

Some debugging tips. Set a breakpoint by clicking to the left of the first line of your code (Set wbsource). The line should be highlighted in brown. Place your cursor in the code and hit F5. The code will stop at your breakpoint. Step through the lines 1 at a time by pressing F8.

- You can print variable values in the Immediate window to see what is going on, or "watch" them. If an error occurs you can often change a line, move the yellow pointer back up and rerun certain lines.

Well that's enough for this session!
 

shn.ea

New Member
Joined
Dec 3, 2009
Messages
7
Yes, both workbooks are in the same directory. Here is the code I have so far (just trying to copy cell O7:S7 for now):

Code:
Sub MedRecLTC()
    
    Dim WbSource As Workbook
    Dim WbDestination As Workbook
    
    Set WbSource = ThisWorkbook
    'Set WbDestination = Workbooks("MedRec-LTC_2_2.0 Mean Number of Unintentional Discrepancies in Long Term Care.xls")
        
    'Set WbDestination = ThisWorkbook.Path & "\MedRec-LTC_1_Generic_NEW.xls"
        
    ChDir (ThisWorkbook.Path)
    Destination = "MedRec-LTC_1_Generic_NEW.xls"
        
    'Workbooks("WbSource").Sheets("Data Entry Sheet").Range("O7").Copy
    'Workbooks("WbDestination").Sheets("Data Entrey Sheet").Range("O7").Paste
    
    Workbooks.Open (Destination)
    Set WbDestination = Workbooks(Destination)
    
     WbSource.Sheets("Data Entry Sheet").Range("O7:S7").Copy
     WbDestination.Sheets("Data Entry Sheet").Range("O7:S7").Paste

    
End Sub

I get an error on the last line (WbDestination.Sheets("Data Entry Sheet").Range("O7:S7").Paste) saying that "Object doesn't support this property or method). What is the correct syntax?
 

Jobowo

Board Regular
Joined
Oct 21, 2009
Messages
242
Well we could track that error down but could you try the simpler (and more efficient):


WbDestination.Sheets("Data Entry Sheet").Range("O7:S7") = _
WbSource.Sheets("Data Entry Sheet").Range("O7:S7")

[/CODE][/QUOTE]

I find copy/paste quirky. I will try to track down the proper syntax for you for another time but you should just use the equates if you can.

PS You don't have to use the line-continuation (_) if you don't need it.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,971
Messages
5,599,110
Members
414,289
Latest member
sonintebil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top