Range.Copy and error 1004

[Carl Rapson]

I have a large named range that I want to save to another workbook, and I'm
trying to use the following sequence:

Range("FS_OUTPUT").Copy
Workbooks.Add
ActiveSheet.Paste
ActiveWorkbook.SaveAs ...
ActiveWorkbook.Close

I'm doing this because I don't want to affect the current workbook file
name -- I want to save the range to another file, keeping the original file
intact. All of this works except for the Range.Copy; at the Range.Copy line
I'm getting the following message:

Run-time error '1004':
Method 'Range' of object '_Global' failed

The range is a pretty large one (it spans several worksheets). I looked up
error 1004 in the Knowledge Base, and it looks like the problem might be
that there is too much data -- maybe the clipboard is getting overloaded?
One KB article said something about periodically saving the workbook during
the copy. Might that be the problem? If it is, I'm not sure how to get
around the problem except to maybe split up my named range into several
ranges, and do each one individually to the same workbook.

Has anyone else ever run into a similar problem?

Thanks,

Carl Rapson

 

[Joe B.]

Try this
Application.Goto Reference:="FS_OUTPUT
'That will select the range from VB
Selection.Cop
'Which will now copy that selection

Joe B.

 

[D.S.]

Carl,
Your code works on my machine. Is your FS_OUTPUT range actually named in upper case?

D.S.
(e-mail address removed)
I have a large named range that I want to save to another workbook, and I'm
trying to use the following sequence:

Range("FS_OUTPUT").Copy
Workbooks.Add
ActiveSheet.Paste
ActiveWorkbook.SaveAs ...
ActiveWorkbook.Close

I'm doing this because I don't want to affect the current workbook file
name -- I want to save the range to another file, keeping the original file
intact. All of this works except for the Range.Copy; at the Range.Copy line
I'm getting the following message:

Run-time error '1004':
Method 'Range' of object '_Global' failed

The range is a pretty large one (it spans several worksheets). I looked up
error 1004 in the Knowledge Base, and it looks like the problem might be
that there is too much data -- maybe the clipboard is getting overloaded?
One KB article said something about periodically saving the workbook during
the copy. Might that be the problem? If it is, I'm not sure how to get
around the problem except to maybe split up my named range into several
ranges, and do each one individually to the same workbook.

Has anyone else ever run into a similar problem?

Thanks,

Carl Rapson

 

[Carl Rapson]

Yes it is, but just to be sure I tried it as lower-case, and it didn't make
a difference. Thanks for the suggestion, though.

I still suspect it might be related to the amount of data I'm trying to
copy. I'm going to mess around with it, and see what I can come up with. I
may have to create several named ranges and copy them one-by-one to get what
I want.

Carl

Carl,
Your code works on my machine. Is your FS_OUTPUT range actually named in
upper case?

D.S.
(e-mail address removed)

 

[Carl Rapson]

Thanks for the suggestion. I tried that, and I again got the 1004 error,
this time with the message "Reference is not valid". Becoming suspicious, I
tried redefining the named range to span only a single worksheet. When I did
that, both my original code and your suggestion worked. So, I am now pretty
certain that the problem is related to the named range spanning multiple
worksheets (either there is too much data, or else the Copy just doesn't
like to span multiple worksheets). I guess I am going to have to look at
splitting up the named range into one name for each worksheet, and
copy/paste each one.

Thanks for the information, I think it helped put me on the right track.

Carl Rapson

 

[Dave Peterson]

I think you hit the nail on the head with your name spanning multiple sheets.