Thread: Moving between variable workbooks

Hi,

I have a basic macro to copy data from a workbook (which is not always the same name) to another workbook (the name of this workbook is constant.

Here is the code:

Sub Copy()

    Dim myworkbook As workbook
    Set myworkbook = ActiveWorkbook
    
        Cells.Select
        Selection.Copy
        Windows("TEST WORKBOOK.xls").Activate
        Cells.Select
        ActiveSheet.Paste
        Range("A2").Select
        Workbooks("myworkbook").Activate
        
End Sub

This produces an "out of range" error. Is this because the macro has moved to the TEST WORKBOOK and can therefore, no loner find "myworkbook" ....?

Does anyone know how I can get around this issue or if it is even possible.

Thanks

Hi,

I don't know if I understand what you want to do
I provied following solution:

Sub CopyIt()
    Dim WB, WB2 As Workbook
    Dim secoundFileName As String
    Set WB = ActiveWorkbook
    secoundFileName = "2.xls"

    Set WB2 = Workbooks.Open(WB.Path & "\" & secoundFileName) 'open secound file
    'copy cells from sheet1 in secound file to first empty row in column A in activeworkbook
    WB2.Sheets(1).UsedRange.Copy WB.Sheets(1).Range("A" & WB.Sheets(1).UsedRange.Rows.Count + 1)
    WB2.Close False 'close secound file
End Sub

In attachments you will find two examples files. Put them into the same folder and play 1.xls Press button for see results. Whole code is in module1.

Best Regards