Paste special from multiple workbooks in a folder to a master workbook

[Hankthetanker]

Hi Excel PROs

I need some help. I'm trying to paste values from all files in a folder to a master folder but i keep getting errors in the paste process. This is my code:

Sub LoopThrough()Dim MyFile As String
Dim erow
Dim FilePath As String
Dim DestWB As Workbook
Dim SourceWB As Workbook


'this way we know the one where the code is running and the destination for our copies
Set DestWB = ThisWorkbook


FilePath = "C:\data\"
MyFile = Dir(FilePath)


Do While Len(MyFile) > 0
If MyFile = "Master.xlsm" Then
Exit Sub
End If


Set SourceWB = Workbooks.Open(FilePath & MyFile)
Workbooks.Open (FilePath & MyFile)
Range("A1:L51").Copy
'Move the close to AFTER you do the paste
'NOTE: You may have to make a change here:
DestWB.Range(Cells(erow, 1), Cells(erow, 12)).PasteSpecial xlValues
SourceWB.Close False
MyFile = Dir
Loop


End Sub

Can i please get some help?

 

[dmt32]

Hi,
welcome to the board.

Not tested but see if this update to your code does what you want.

Sub LoopThrough()
    Dim MyFile As String
    Dim FilePath As String
    Dim DestWS As Worksheet
    Dim SourceWB As Workbook
    
    
    'Master worksheet - change name as required
    Set DestWS = ThisWorkbook.Worksheets("Sheet1")
    
    
    FilePath = "C:\data\"
    MyFile = Dir(FilePath)
    
    Application.ScreenUpdating = False
    On Error GoTo myerror
    Do While Len(MyFile) > 0
    If MyFile <> "Master.xlsm" Then


    Set SourceWB = Workbooks.Open(FilePath & MyFile)
    
        SourceWB.Sheets(1).Range("A1:L51").Copy
        
        DestWS.Range("A" & DestWS.Cells(DestWS.Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial xlValues
        
        Application.CutCopyMode = False
        
        SourceWB.Close False
    
    MyFile = Dir
    End If
    Set SourceWB = Nothing
    Loop
    
myerror:
Application.ScreenUpdating = True
If Err > 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

You will need to change the master sheet name shown in RED as required.

Hope Helpful

Dave

 

[Hankthetanker]

Dave, Thank you so much for helping me. You have just saved me many hours every week from now. How would the world be without people like you.

 

[dmt32]

Dave, Thank you so much for helping me. You have just saved me many hours every week from now. How would the world be without people like you.

I don't always get things right going bit daft in my old age but glad adjustments helped you.

Many thanks for your kind feedback, very much appreciated.

Dave

 

[Hankthetanker]

Darn. Excel accualy crashes when i run it :/

 

[Hankthetanker]

Oh my god. It was easily fixed by removing the master file out of the folder. Many thanks again.

 

[dmt32]

Darn. Excel accualy crashes when i run it :/

crashes how?