Error 438

[DecisionMaking]

Hi All, I'm trying to create a macro in order to load data from other excel files into a file. However, I get the error 438 'object doesn't support this property or...' Unfortunately I cant figure out what Im doing wrong because im not a vba expert. I used the following code. I underlined the part when debug gives the code. Hope someone can help me out!! Thank you -----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

 

[Norie]

Try this.

Workbooks(fileName).Worksheets(sheet.Name).Copy _
after:=Workbooks("Canon_Final_Model0.1").Worksheets(total)

 

[DecisionMaking]

Yesss, it worked! Thanks a lot!!

 

[DecisionMaking]

Hi, I had to adjust the code I previously posted here because we started working on a MacBook instead of windows. Guess what, now the code does not work . Hope someone can help me out! It has something to do with the filename after directory, see below. In bold is the part where the bug is. In the directory, there are several files which always start with 'Carrier ...' These files are either xls or xlm. The macro should import all sheets of these workbooks into my 'Final Model'. Would be great if you can help!Sub Macro61()

Dim directory As String, fileName As String, sheet As Worksheet, total As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False

directory = "Macintosh HD:Users:denise:Documents:Canon Final Model:"

fileName = Dir(directory & "*xl")


Do While fileName <> ""

Workbooks.Open (directory & fileName)

For Each sheet In Workbooks(fileName).Worksheets
total = Workbooks("Canon_Final_Model_VU_Tolsma_Jeukens_Grubben").Worksheets.Count
Workbooks(fileName).Worksheets(sheet.Name).Copy _
After:=Workbooks("Canon_Final_Model_VU_Tolsma_Jeukens_Grubben").Worksheets(total)
Next sheet

Workbooks(fileName).Close

fileName = Dir()

Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True


End Sub

 

[Norie]

What's the actual problem?

Are you getting errors? Is the code not working as expected?

 

[DecisionMaking]

Apparently the code cannot find the documents in the folder..