Copy Paste Between Workbooks VBA

[beginvbaanalyst]

Hi Everyone,

How do I copy and paste work between workbooks (I have a total of five open workbooks)?
My Range will be unlimited such as :
Rows.Count).End(xlUp))
I see people do two typically but I don't know how to write when there's more than two.
I have one main workbook that I will be using; the other four are scripts I'm pasting into to run files.
How would I go about doing this this?
Currently I have:

Sub OpenScriptsForExecution()

'Opens Listing Script From A Drive
Workbooks.Open Filename:="A:\Scripts Library\WSM3_Listing_Template_9.17.18.xlsx"

'Opens Delisting Script From A Drive
Workbooks.Open Filename:="A:\Scripts Library\WSM3_DELIST.xlsx"

'Opens Exclusion Script From A Drive
Workbooks.Open Filename:="A:\Scripts Library\WSO1_WSO5_CREATE_Assortment_Exclusion_Module.xlsx"

'Opens Deletion of Exclusion Module From A Drive
Workbooks.Open Filename:="A:\Scripts Library\WSO2_Delete_First_Line_9.17.18.xlsx"

End Sub

Sub CopyWorkbooks()

Dim WorkbookQuery As Workbook
Dim WorkbookList As Workbook
Dim WorkbookDelist As Workbook
Dim WorkbookExclusion As Workbook
Dim WorkbookDeleteExclusion As Workbook

This opens the workbooks but now I'm stuck.
I understand I need to set my Dims, could I just remove the first process all together?
Please help.
Thank you.

 

[dmt32]

Hi,

See if following code is of any help to you

Sub CopyWorkbooks()
    Dim FileNames As Variant, FileName As Variant
    Dim wbCopyFrom As Workbook, wbCopyTo As Workbook
    Dim NextRow As Long
    
'the workbook you are copying data to
    Set wbCopyTo = ThisWorkbook
    
'Files From A Drive
    FileNames = Array("A:\Scripts Library\WSM3_Listing_Template_9.17.18.xlsx", _
                       "A:\Scripts Library\WSM3_DELIST.xlsx", _
                        "A:\Scripts Library\WSO1_WSO5_CREATE_Assortment_Exclusion_Module.xlsx", _
                        "A:\Scripts Library\WSO2_Delete_First_Line_9.17.18.xlsx")
                        
     Application.ScreenUpdating = False
    For Each FileName In FileNames
'do copy stuff here - following is an example only

'open CopyFrom workbook readonly
        Set wbCopyFrom = Workbooks.Open(FileName, False, True)
    
        With wbCopyTo.Sheets(1)
'get next empty row to paste to
            NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
'copy usedrange from copyfrom workbook sheet 1 and
'paste to next blank row in copyto workbook sheet 1
            wbCopyFrom.Sheets(1).UsedRange .Cells(NextRow, 1)
        End With
        
'close but do not save
        wbCopyFrom.Close False
        Set wbCopyFrom = Nothing
    Next FileName
    Application.ScreenUpdating = True
End Sub

Above should open each workbook (CopyFrom) listed in array in turn and copy the used range in sheet 1. This is then pasted to next blank row in sheet 1 (CopyTo) of the master workbook

The CopyFrom workbook is then closed without saving & code loops around to open next workbook & repeat the process.

This is only a suggestion & you will need to adjust to meet your specific project need as required

Others here may have alternative suggestions



Dave

 

[beginvbaanalyst]

Hi,

See if following code is of any help to you

Sub CopyWorkbooks()
    Dim FileNames As Variant, FileName As Variant
    Dim wbCopyFrom As Workbook, wbCopyTo As Workbook
    Dim NextRow As Long
   
'the workbook you are copying data to
    Set wbCopyTo = ThisWorkbook
   
'Files From A Drive
    FileNames = Array("A:\Scripts Library\WSM3_Listing_Template_9.17.18.xlsx", _
                       "A:\Scripts Library\WSM3_DELIST.xlsx", _
                        "A:\Scripts Library\WSO1_WSO5_CREATE_Assortment_Exclusion_Module.xlsx", _
                        "A:\Scripts Library\WSO2_Delete_First_Line_9.17.18.xlsx")
                       
     Application.ScreenUpdating = False
    For Each FileName In FileNames
'do copy stuff here - following is an example only

'open CopyFrom workbook readonly
        Set wbCopyFrom = Workbooks.Open(FileName, False, True)
   
        With wbCopyTo.Sheets(1)
'get next empty row to paste to
            NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
'copy usedrange from copyfrom workbook sheet 1 and
'paste to next blank row in copyto workbook sheet 1
            wbCopyFrom.Sheets(1).UsedRange .Cells(NextRow, 1)
        End With
       
'close but do not save
        wbCopyFrom.Close False
        Set wbCopyFrom = Nothing
    Next FileName
    Application.ScreenUpdating = True
End Sub

Above should open each workbook (CopyFrom) listed in array in turn and copy the used range in sheet 1. This is then pasted to next blank row in sheet 1 (CopyTo) of the master workbook

The CopyFrom workbook is then closed without saving & code loops around to open next workbook & repeat the process.

This is only a suggestion & you will need to adjust to meet your specific project need as required

Others here may have alternative suggestions



Dave

Hi Dave,

I'll give more context. Each array has it's own specific sheet in my entire Active Workbook.
I was thinking moving back and forth between each open workbook and making them active to copy and paste.
I do this when I copy and paste in a single workbook but with multiple sheets.
Is what you wrote the same method or more complex?

For instance:
I'd copy Column A and B from sheet 4 and paste into A:\Scripts Library\WSM3_Listing_Template_9.17.18.xlsx" in column A, row 2.
I appreciate all the help, this definitely helps open new doors for me.

 

[dmt32]

Hi,
when you open a workbook using WorkBooks.Open method it becomes the active workbook but it does not need to be active to work with it like copying & pasting.

Example code I provided shows how to use object variables to reference each workbook & use these to take required action(s) & hopefully, gives you something to work with.

Suggest you have a play & see if can adjust to meet your project need but post back if require further assistance.

Dave