Copy data between two workbooks

[shn.ea]

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:

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:

    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?

 

[Jobowo]

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.

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:

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!

 

[shn.ea]

Thanks! The use of arrays is interesting, I will give it a try.

Currently I'm running into my original problem "Compile error: Method or data member not found." in relation to the sheets in WbSource.Sheets("Data Entry Sheet").Range("O7")

It seems the current code is not setting my WbSource and WbDestination properly.

My sample worksheets are:
http://rapidshare.com/files/315907738/MedRec-LTC_1_HospitalName_0909_OLD.xls
http://rapidshare.com/files/315907662/MedRec-LTC_1_Generic_NEW.xls

 

[Jobowo]

Sorry, should have noticed: that would be (singular)

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

 

[shn.ea]

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]

Hi!

Try this 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]

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

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

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

 

[Jobowo]

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]

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):

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]

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.