Activating a workbook
 

I have 2 files open already when I run the macro - ("Q10 - Summary
Comparison.xls") & ("USPDS_2008_Q10_Summary_data.xls"). All I am
trying to do is activate the workbook that is not currently active and
close it and then kill it. For some reason my code is not working, it
gives me the error message "Subscript out of range" and I don't know
why. The file names are correct, it is probably just something simple
but I just can't see it. Below is my code.

Sub UpdateReport()

Dim c As Long


Sheets("Raw Data").Select
For c = 1 To 16
Columns(c).EntireColumn.Hidden = False
Next c

For c = 1 To 16

If (Cells(2, c).Value) = "" Then
Columns(c).EntireColumn.Hidden = True
Else
Columns(c).EntireColumn.Hidden = False
End If
Next c

Windows("USPDS_2008_Q10_Summary_data.xls").Activat e ' Gives me
error Subscript out of Range'
ActiveWindow.Close

Kill "C:\OPEX Folder\BSC Graphs\data files
\USPDS_2008_Q10_Summary_data.xls"

Windows("Q10 - Summary Comparison.xls").Activate
Sheets("Q10 - U.S. PDS Summary").Select

ActiveSheet.Shapes("Button 4").Select
Selection.Delete

ActiveWorkbook.SaveAs Filename:= _
"C:\OPEX Folder\BSC Graphs\Q10 - Summary Comparison " &
Format(Date, "mmddyyyy") & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End Sub

Activating a workbook
 

Change
Windows("USPDS_2008_Q10_Summary_data.xls").Activat e
ActiveWindow.Close

to
Workbooks("USPDS_2008_Q10_Summary_data.xls").Activ ate
ActiveWorkbook.Close SaveChanges:=True ' or False, your choice
or simply
Workbooks("USPDS_2008_Q10_Summary_data.xls").Close SaveChanges:=True


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


wrote in message
...
I have 2 files open already when I run the macro - ("Q10 - Summary
Comparison.xls") & ("USPDS_2008_Q10_Summary_data.xls"). All I am
trying to do is activate the workbook that is not currently active and
close it and then kill it. For some reason my code is not working, it
gives me the error message "Subscript out of range" and I don't know
why. The file names are correct, it is probably just something simple
but I just can't see it. Below is my code.

Sub UpdateReport()

Dim c As Long


Sheets("Raw Data").Select
For c = 1 To 16
Columns(c).EntireColumn.Hidden = False
Next c

For c = 1 To 16

If (Cells(2, c).Value) = "" Then
Columns(c).EntireColumn.Hidden = True
Else
Columns(c).EntireColumn.Hidden = False
End If
Next c

Windows("USPDS_2008_Q10_Summary_data.xls").Activat e ' Gives me
error Subscript out of Range'
ActiveWindow.Close

Kill "C:\OPEX Folder\BSC Graphs\data files
\USPDS_2008_Q10_Summary_data.xls"

Windows("Q10 - Summary Comparison.xls").Activate
Sheets("Q10 - U.S. PDS Summary").Select

ActiveSheet.Shapes("Button 4").Select
Selection.Delete

ActiveWorkbook.SaveAs Filename:= _
"C:\OPEX Folder\BSC Graphs\Q10 - Summary Comparison " &
Format(Date, "mmddyyyy") & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End Sub