"Subscript Out of Range" Error Question

[LarryP]

When I am working in workbook xxxxx, the code below is supposed to open
workbook yyyyy, copy an entire sheet from it, come back to workbook xxxxx,
paste the copied data into a corresponding sheet in xxxxx (overwriting any
previous contents), then shut down workbook yyyyy. However, the line that
takes me back to workbook yyyyy so I can close it (I've set it off with
###########) is throwing a "subscript out of range" error -- this even though
I know darn well that yyyyy exists, I just got done copying from it!! Any
ideas on why and what I can do about it are most welcome.






'Get the most current HeadersnTitles data from HeadersnTitles.xls in
c:\LOBTmplat\Template
Workbooks.Open Filename:="C:\LOBTmplat\Template\HeadersnTitles.xls"
Cells.Select
Selection.Copy
Windows("Auto_PRISM_Lob.xls").Activate
Sheets("HeadersnTitles").Select
Cells.Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Names.Add Name:="Titles",
RefersToR1C1:="=HeadersnTitles!R3C1:R23C1"
ActiveWorkbook.Names.Add Name:="DefaultHeaders",
RefersToR1C1:="=HeadersnTitles!R1C2:R1C55"
Sheets("Auto_PRISM_Lob").Select
##########################################
Windows("HeadersnTitles.xls").Activate
##########################################
ActiveWindow.Close

 

[Bob Phillips]

Try this

Dim WB As Workbook

'Get the most current HeadersnTitles data from HeadersnTitles.xls in
c:\LOBTmplat\Template
Set WB =
Workbooks.Open(Filename:="C:\LOBTmplat\Template\HeadersnTitles.xls")
WB.Worksheets(1).Cells.Copy
Wokbooks("Auto_PRISM_Lob.xls").Sheets("HeadersnTitles").Range("A1")
Workbooks("Auto_PRISM_Lob.xls").Names.Add Name:="Titles",
RefersToR1C1:="=HeadersnTitles!R3C1:R23C1"
Workbooks("Auto_PRISM_Lob.xls").Names.Add Name:="DefaultHeaders",
RefersToR1C1:="=HeadersnTitles!R1C2:R1C55"
WB.Close savechanges:=False


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[LarryP]

How quickly we forget -- I just got that answer a couple weeks ago (probably
from you!!), but had already forgotten about the workbook reference going out
of scope under certain conditions. Will try harder to remember, like paint
it on my forehead with a magic marker or something. Thanks, Bob.