VBA for 5 files to Master

[RR786]

I have about 5 files with data in columns A through BU. All 5 files have different names but 'data' is in worksheet.

i need a VBA to consolidate the data thats re all different length but only keeping current title in the Master file called 'Master'


Trying to get a VBA code to:

1. Clear data from master file
2. Open each of the 5 files. (TASS, MUC, SAS etc) but common start is 'Underlying Bridge - '
3. copy columns row 2 to where ever the data goes to and then add additional from file 2,3,4 etc
3. paste data tab within master file.

Can this be done? Thanks in advance.

 

[RR786]

nope still getting an error but this time its run-time error 424

 

[JLGWhiz]

Which line of code?

 

[RR786]

this is what i have :

Run-time error '424;: Object Required

seems like it after the copy - its unable to paste

Private Sub CommandButton1_Click()
Dim sh As Worksheet, wb As Workbook, fPath As String, fName As String
fPath = "M:\20-21\Business Planning\Budgets\"
Set sh = ThisWorkbook.Sheets("Data")
sh.UsedRange.Offset(1).ClearContents
fName = Dir(fPath & "*.xls*")
    Do While fName <> ""
        If fName <> ThisWorkbook.Name Then
            Set wb = Workbooks.Open(fPath & fName)
            wb.Sheets("Data").UsedRange.Offset(1).Copy
            sh.Cells(Row.Count, 1).End(x1Up)(2).PasteSpecial xlPasteValues
            wb.Close False
        End If
        fName = Dir
    Loop
End Sub

 

[JLGWhiz]

Delete the old code and use this one.

Private Sub CommandButton1_Click()
Dim sh As Worksheet, wb As Workbook, fPath As String, fName As String
fPath = "M:\20-21\Business Planning\Budgets\"
Set sh = ThisWorkbook.Sheets("Data")
sh.UsedRange.Offset(1).ClearContents
fName = Dir(fPath & "*.xls*")
    Do While fName <> ""
        If fName <> ThisWorkbook.Name Then
            Set wb = Workbooks.Open(fPath & fName)
            wb.Sheets("Data").UsedRange.Offset(1).Copy
            sh.Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValues
            Application.CutCopyMode = False
            wb.Close False
        End If
        fName = Dir
    Loop
End Sub

 

[RR786]

thanks its working!

 

[JLGWhiz]

thanks its working!

You're welcome,
regards, JLG