Link to home
Start Free TrialLog in
Avatar of route217
route217Flag for United Kingdom of Great Britain and Northern Ireland

asked on

Tricky copy and paste macro from multiple workbooks

Hi Experts

Need a macro that opens all the workbooks in the c: drive file paths c:\documents and setting\test

One by one and copies the data in worksheet "cm" and and pastes the data onto a master workbook same file path called "Master Template" and into worksheet "cm1". So the data is stacked one under Neath the other...

Workbooks in c: drive file paths c:\documents and setting\test are:-

1. Customer.xls
2. Supplier.xls
3. Temporary.xls
4. Resource.xls
Avatar of Norie
Norie
Try this which assumes the Master Template worksheet needs to be opened.
Sub Conso()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim strFilename As String

    Set wbDst = Workbooks.Open("C:\Documents and Settings\Test\Master Template.xls")
    
    strFilename = Dir("C:\Documents and Settings\Test\*.xls")
    
    While strFilename <> ""
    
        If strFilename <> "Master Template.xls" Then
        
            Set wbSrc = Workbooks.Open("C:\Documents and Settings\Test\" & strFilename)
            
                 wbSrc.Worksheets("cm").UsedRange.Copy wbDst.Worksheets("cm1").Range("A" & Rows.Count).End(xlUp).Offset(1)

            
            wbSrc.Close
        End If
        
        strFilename = Dir()
        
    Wend
                 
End Sub

Open in new window

Avatar of route217
route217
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Immoire

Thanks for the feedback... Are saying run the macro from the master file?
Avatar of route217
route217
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Ps last question

Immoire this macro will open up all the workbook in the specified c: drive one by one and paste the data into the master file one after the other...
Avatar of Norie
Norie
It will open the master workbook, then one by one open the rest of the xls files in the folder, copy the data from sheet 'cm' to sheet 'cm1' in the master workbook.

If you want to run it from the master workbook it can easily be changed.
Avatar of route217
route217
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

That would be great if it could be changed to run from master file ... And is the data pasted into master file one after another I.e each file in turn??
ASKER CERTIFIED SOLUTION
Avatar of Norie
Norie
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial