stanleds
10-10-2013, 01:32 AM
Please can somebody offer some help? I am trying to modify an existing macro that used to work in Excel 2002. This failed to work after an upgrade to office 2010. i have modified the code and managed to get over the initial problem but I now get an error that I do not understand.
The code is intended to open a series of Excel files, one at a time. Copy the required data and then paste the data into the main file before closing each Excel file without saving the changes.
It gives me an "Run-time error 91: Object variable or with block variable not set at the line below wbMyFile.Close False.
I feel that this code is probably very poorly written as i have very little experience of actually writing code. Any help would be gratefully received.
many thanks
stanleds
Sub Update_Compare_Month()
Call Create_Blank1
Application.StatusBar = "Refreshing Meter List, please be patient!......."
Dim MyFolder As String
Dim MyFile As String
Dim wb As Workbook, wbMyFile As Workbook
MyFolder = Sheets("Named Ranges").Range("H3").Value
MyFile = Dir(MyFolder & "\*.xls")
Do While MyFile <> ""
Workbooks.Open FileName:=MyFolder & "\" & MyFile
MyFile = Dir
Cells.Select
Selection.EntireColumn.Hidden = False
Call Autofilter
Selection.Autofilter Field:=3, Criteria1:="0"
Range("A2").Offset(1, 0).Select
Range(ActiveCell, Range("M" & Rows.Count).End(xlUp)).Select
Selection.SpecialCells(xlCellTypeVisible).Copy
Workbooks("No Reads Grabber - COPY.xls").Activate
Sheets("Named Ranges").Select
Worksheets(Range("U3").Value).Activate
Range("A65535").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
wbMyFile.Close False
Loop
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Call Unmerge_All
Call Format_Date
Application.StatusBar = False
'Call Copy_C
Call Copy_OPQRS
'Call Copy_Q
Call Insert_Row
Call Format_Meter_Ref
MsgBox ("Comparison Month - Update Complete")
sFilename = Dir
End Sub
The code is intended to open a series of Excel files, one at a time. Copy the required data and then paste the data into the main file before closing each Excel file without saving the changes.
It gives me an "Run-time error 91: Object variable or with block variable not set at the line below wbMyFile.Close False.
I feel that this code is probably very poorly written as i have very little experience of actually writing code. Any help would be gratefully received.
many thanks
stanleds
Sub Update_Compare_Month()
Call Create_Blank1
Application.StatusBar = "Refreshing Meter List, please be patient!......."
Dim MyFolder As String
Dim MyFile As String
Dim wb As Workbook, wbMyFile As Workbook
MyFolder = Sheets("Named Ranges").Range("H3").Value
MyFile = Dir(MyFolder & "\*.xls")
Do While MyFile <> ""
Workbooks.Open FileName:=MyFolder & "\" & MyFile
MyFile = Dir
Cells.Select
Selection.EntireColumn.Hidden = False
Call Autofilter
Selection.Autofilter Field:=3, Criteria1:="0"
Range("A2").Offset(1, 0).Select
Range(ActiveCell, Range("M" & Rows.Count).End(xlUp)).Select
Selection.SpecialCells(xlCellTypeVisible).Copy
Workbooks("No Reads Grabber - COPY.xls").Activate
Sheets("Named Ranges").Select
Worksheets(Range("U3").Value).Activate
Range("A65535").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
wbMyFile.Close False
Loop
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Call Unmerge_All
Call Format_Date
Application.StatusBar = False
'Call Copy_C
Call Copy_OPQRS
'Call Copy_Q
Call Insert_Row
Call Format_Meter_Ref
MsgBox ("Comparison Month - Update Complete")
sFilename = Dir
End Sub