View Full Version : VBA Combine multiple workbooks into one master HELP!
cyy1315
06-06-2017, 03:36 AM
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
06-06-2017, 03:57 AM
You appear to be missing a drive letter such as
directoryPath = "C:/Users/cathy/Documents/vba/"
cyy1315
06-06-2017, 04:13 AM
I added it and it says path not founded
cyy1315
06-06-2017, 04:16 AM
error 76
mdmackillop
06-06-2017, 05:03 AM
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
06-06-2017, 06:41 AM
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
06-06-2017, 06:59 AM
i looked it up at the Terminal of Macbook so I assume the address should be correct?
Jan Karel Pieterse
06-06-2017, 07:29 AM
I don't think MAC Excel allows the use of the Dir function in VBA I'm afraid.
cyy1315
06-06-2017, 07:31 AM
oh really?! Thanks for reminding me
cyy1315
06-06-2017, 08:48 AM
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
06-06-2017, 09:42 PM
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
06-06-2017, 11:00 PM
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
06-07-2017, 12:22 AM
I am not sure what you are asking for precisely? The code already copies and pastes?
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.