"Subscript Out of Range" Error Question
 

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.xl s"
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

"Subscript Out of Range" Error Question
 

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\He adersnTitles.xls")
WB.Worksheets(1).Cells.Copy
Wokbooks("Auto_PRISM_Lob.xls").Sheets("HeadersnTit les").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" wrote in message
...
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.xl s"
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

"Subscript Out of Range" Error Question
 

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.

"LarryP" wrote:

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.xl s"
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