PDA

View Full Version : Error 438 in VBA



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

mancubus
11-26-2014, 07:15 AM
hi.

donot use reserved words as variable names, ie, sheet.

when you open a workbook it becomes the active workbook. so i used ActiveWorkbook object.
it seems you missed "Worksheets" before "total".

ScreenUpdating and DisplayAlerts automatically sets to TRUE after the code is executed. (Office2007+)



Sub Import_Quoations()
Dim directory As String, fileName As String
Dim ws As Worksheet
Dim total As Long, calc As Long

With Application
.DisplayAlerts = False
.ScreenUpdating = False
.EnableEvents = False
calc = .Calculation
.Calculation = xlCalculationManual
.AskToUpdateLinks = False
End With

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
ActiveWorkbook.Worksheets(ws.Name).Copy _
After:=Workbooks("Canon_Final_Model0.1").Worksheets(total)
Next ws
ActiveWorkbook.Close False
fileName = Dir()
Loop

With Application
.EnableEvents = True
.Calculation = calc
.AskToUpdateLinks = True
End With
End Sub



PS:
pls use code tags when posting your code here.
clicking the # button will do it for you.

Charlize
11-27-2014, 05:56 AM
Please, don't post your problem on different locations on this forum : http://www.vbaexpress.com/forum/showthread.php?51241-Error-438-in-VBA-help-)

Charlize