Change
Windows("USPDS_2008_Q10_Summary_data.xls").Activate
ActiveWindow.Close
to
Workbooks("USPDS_2008_Q10_Summary_data.xls").Activate
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)
<(E-Mail Removed)> wrote in message
news:0fa0f038-4036-4519-a052-(E-Mail Removed)...
>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").Activate ' 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
Similar Threads
