Decision
11-26-2014, 06:41 AM
Hi All,
I created a macro to load worksheets from other excel files into a file. However, I get an error 438 saying that the object doesnt support this property etc. Since I am not that familiar with excel VBA I am hoping someone can help me out. Below you find the code. In bold is the part where the bug/error is. Thanks a lot!
Sub Import_Quoations()
Dim directory As String, fileName As String, sheet As Worksheet, total As Integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False
directory = "D:\VU\Decision Making\Final Model\"
fileName = Dir(directory & "Carrier*?")
Do While fileName <> ""
Workbooks.Open (directory & fileName)
For Each sheet In Workbooks(fileName).Worksheets
total = Workbooks("Canon_Final_Model0.1").Worksheets.Count
Workbooks(fileName).Worksheets(sheet.Name).Copy _
after:=Workbooks("Canon_Final_Model0.1")(total)
Next sheet
Workbooks(fileName).Close
fileName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
I created a macro to load worksheets from other excel files into a file. However, I get an error 438 saying that the object doesnt support this property etc. Since I am not that familiar with excel VBA I am hoping someone can help me out. Below you find the code. In bold is the part where the bug/error is. Thanks a lot!
Sub Import_Quoations()
Dim directory As String, fileName As String, sheet As Worksheet, total As Integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False
directory = "D:\VU\Decision Making\Final Model\"
fileName = Dir(directory & "Carrier*?")
Do While fileName <> ""
Workbooks.Open (directory & fileName)
For Each sheet In Workbooks(fileName).Worksheets
total = Workbooks("Canon_Final_Model0.1").Worksheets.Count
Workbooks(fileName).Worksheets(sheet.Name).Copy _
after:=Workbooks("Canon_Final_Model0.1")(total)
Next sheet
Workbooks(fileName).Close
fileName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub