folks, I am getting a "subscript out of range" error when trying to close a workbook.
I have used a message box to confirm that the variable still holds the file name which it does. so why doesn't it work?
Code:
Sub GetWeeklyReportPoints()
'///declare variables
Dim WklyRptPtsFileName As Variant
Dim RptSht As Variant
Dim TopCell, BottomCell As Range
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
ChDrive "Z"
ChDir "Z:\REPORTS\002 Weekly Reports\Weekly Reports 2009"
Set RptSht = Worksheets("Group Procurement + PE + Anne B")
'application input box question
If MsgBox("Obtain the latest Weekly Report Points file at:" & _
vbLf & vbLf & "Z:\REPORTS\002 Weekly Reports\Weekly Reports 2009", _
vbOKCancel, "Weekly Report Points") <> vbOK Then Exit Sub
Application.Goto Reference:="StartWklyPoints"
Set TopCell = ActiveCell.Offset(rowOffset:=1, columnOffset:=0)
Application.Goto Reference:="EndWklyPoints"
Set BottomCell = ActiveCell.Offset(rowOffset:=-1, columnOffset:=0)
RptSht.Range(TopCell, BottomCell).Delete
WklyRptPtsFileName = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If WklyRptPtsFileName = False Then Aborted = True: Exit Sub
With Workbooks.Open(WklyRptPtsFileName)
myShts = ActiveWorkbook.Sheets.Count
For i = 1 To myShts
myList = myList & i & " - " & ActiveWorkbook.Sheets(i).Name & " " & vbCr
Next i
Dim mySht As Single
mySht = InputBox("Select sheet to go to." & vbCr & vbCr & myList)
With Sheets(mySht)
.UsedRange.Copy
End With
End With
ThisWorkbook.Activate
Application.Goto Reference:="StartWklyPoints"
Set NewTopCell = ActiveCell.Offset(rowOffset:=1, columnOffset:=0)
NewTopCell.Insert (xlShiftDown)
MsgBox WklyRptPtsFileName
Workbooks(WklyRptPtsFileName).Close SaveChanges:=False
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
I have used a message box to confirm that the variable still holds the file name which it does. so why doesn't it work?