VBA Combine multiple workbooks into one master HELP!

[cyy1315]

Hi I am trying to combine multiple workbooks into one master workbooks but it has an error of 54 that cannot find my file. I realized my dir() function does not work in my excel. Could anyone help please???

Sub copypastecolumns()


Dim directoryPath As String
Dim filepath As String
Dim filename As String
Dim Lastrow As Long, Lastcolumn As Long




directoryPath = "/Users/cathy/Documents/vba/"
filepath = directoryPath & "*.xlsx"
filename = Dir(filepath)




MsgBox ("hi")<---- it doesnt even get to here and shows the error of 54,



Do While Len(filepath) > 0
If MyFile = "workbook6.xlsx" Then
Exit Sub
End If



Workbook.Open (filepath & MyFile)
Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(2, 1), Cells(Lastrow, Lastcolumn)).Copy
ActiveWorkbook.Close

erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 6))
MsgBox ("hi2")


MyFile = Dir
Loop




End Sub

[mdmackillop]

You appear to be missing a drive letter such as
directoryPath = "C:/Users/cathy/Documents/vba/"

[cyy1315]

I added it and it says path not founded

[cyy1315]

error 76

[mdmackillop]

I see you are using / instead of \; maybre try the backslash.
or
Copy the path from Explorer to ensure there are no typos and edit it accordingly.

Edit: Where to you get MyFile from?

[Jan Karel Pieterse]

I expect you are using a re-directed path to your documents folder. If you open Explorer and go to your My Documents folder (the one you see as a sub-folder beneath your username, NOT the one under Libraries!) and right-click it and choose Copy address as text, what does it paste precisely?

[cyy1315]

i looked it up at the Terminal of Macbook so I assume the address should be correct?

[Jan Karel Pieterse]

I don't think MAC Excel allows the use of the Dir function in VBA I'm afraid.

[cyy1315]

oh really?! Thanks for reminding me

[cyy1315]

I used windows for this code now, but it only shows the message of "hi", not even the second one. Could you guys help with this please?

Sub copyandpastecolumns()
Dim directoryPath As String
Dim filepath As String
Dim filename As String
Dim Lastrow As Long, Lastcolumn As Long




directoryPath = "C:\Users\Kent\Documents\"
filepath = directoryPath & "*.xlsx"
filename = Dir(filepath)






MsgBox ("hi")


Do While Len(filename) > 0
If filename = "workbook6.xlsx" Then
Exit Sub
End If



MsgBox ("hi2")


Workbook.Open (filepath & MyFile)
Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(2, 1), Cells(Lastrow, Lastcolumn)).Copy
ActiveWorkbook.Close

erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 6))
MsgBox ("hi2")


MyFile = Dir
Loop




End Sub

[Jan Karel Pieterse]

I made a couple of changes.
I advise you to add the Option Explicit at the top of your modules to force variable declaration, this would prevent a couple of errors. (Tools, Options, second checkbox)

Option Explicit
Sub copyandpastecolumns()
    Dim directoryPath As String
    Dim filepath As String
    Dim filename As String
    Dim Lastrow As Long
    Dim Lastcolumn As Long
    Dim erow As Long
 
    directoryPath = "C:\Users\piete\Documents\"
    filepath = directoryPath & "*.xlsx"
    filename = Dir(filepath)
 
 

    MsgBox ("hi")

    Do While Len(filename) > 0
'Think these are not needed
'        If filename = "workbook6.xlsx" Then
'            Exit Sub
'        End If
 
        MsgBox ("hi2")

        Workbooks.Open directoryPath & filename
        Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        Lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
        Range(Cells(2, 1), Cells(Lastrow, Lastcolumn)).Copy
        ActiveWorkbook.Close
        erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        ActiveSheet.Paste Destination:=Worksheets("Sheet1").Cells(erow, 1)
        MsgBox ("hi2")
        'Note the parentheses and use of the correct variable:
        filename = Dir()
    Loop
 

End Sub

[cyy1315]

Hi it works now, but how can i copy and paste the data from newly added workbook? How can i add the button and put it into the code?

[Jan Karel Pieterse]

I am not sure what you are asking for precisely? The code already copies and pastes?