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
route217Asked:
Who is Participating?
 
NorieConnect With a Mentor VBA ExpertCommented:
That's how the data is pasted.

Here's the code so it will run from the master file.
Sub Conso()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim strFilename As String

    Set wbDst = ThisWorkbook  ' Workbooks.Open("C:\Documents and Settings\Test\Master Template.xls")
    
    strFilename = Dir("C:\Documents and Settings\Test\*.xls")
    
    While strFilename <> ""
    
        If strFilename <> wbDst.Name 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

0
 
NorieVBA ExpertCommented:
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

0
 
route217Author Commented:
Immoire

Thanks for the feedback... Are saying run the macro from the master file?
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
route217Author Commented:
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...
0
 
NorieVBA ExpertCommented:
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.
0
 
route217Author Commented:
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??
0
 
route217Author Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.