Chk sheet exits, copy all to another book.

[Karen53]

Hi,

I am trying to copy everything on these three pages on to another sheet in
another workbook. I'm having trouble getting this to work. Can I do it this
way?

ictr = 0

For ictr = 1 To 3
Debug.Print ictr

On Error Resume Next
Select Case ictr
Case 1
Set rngCopyFrom = wbkCopyFrom.Notespg.Cells
Set rngCopyTo = wbkCopyTo.Notespg
Case 2
Set rngCopyFrom = wbkCopyFrom.Firstpg.Cells
Set rngCopyTo = wbkCopyTo.Firstpg
Case 3
Set rngCopyFrom = wbkCopyFrom.Lastpg.Cells
Set rngCopyTo = wbkCopyTo.Lastpg
End Select

On Error GoTo 0
If rngCopyFrom Is Nothing Then
'do nothing
Else
rngCopyTo.Value = rngCopyFrom.Value
End If

Next

 

[Joel]

Try changing this one statement. When copying multiple cells use the copy
method source.copy destination:=dest

from
rngCopyTo.Value = rngCopyFrom.Value
to
rngCopyFrom.copy destination:=rngCopyTo

 

[Dave Peterson]

I'm guessing that Notespg and Firstpg and Lastpg are all codenames for sheets
within each of the workbooks, if that's the case, you're going to have to do
something else.

You can use the codepage of the workbook with the code (without using the
workbook object as the qualifier). But you'll have to use another technique to
find the worksheet that corresponds to the codename in the other workbooks.

In a previous thread, you saw how I looped through the sheets in the workbook
and compared codenames to the name I needed. There are other ways, but I find
this the most reliable--I don't have to worry about any security setting that
other users may have.

 

[Karen53]

Thank yo both for your help!
--
Thanks for your help.
Karen53

I'm guessing that Notespg and Firstpg and Lastpg are all codenames for sheets
within each of the workbooks, if that's the case, you're going to have to do
something else.

You can use the codepage of the workbook with the code (without using the
workbook object as the qualifier). But you'll have to use another technique to
find the worksheet that corresponds to the codename in the other workbooks.

In a previous thread, you saw how I looped through the sheets in the workbook
and compared codenames to the name I needed. There are other ways, but I find
this the most reliable--I don't have to worry about any security setting that
other users may have.

 

[Karen53]

Hi,

When I copy the cells of these pages, I get a message that one of my named
ranges is in the other book as well and askes me which to use. These sheets
being copied are not involved in this named range at all. They have no
formulas. The workbook has many named ranges but this is the only one
popping up. This named range is only used on one worksheet, CAMMaster.

Any ideas as to how I can stop this?
--
Thanks for your help.
Karen53

I'm guessing that Notespg and Firstpg and Lastpg are all codenames for sheets
within each of the workbooks, if that's the case, you're going to have to do
something else.

You can use the codepage of the workbook with the code (without using the
workbook object as the qualifier). But you'll have to use another technique to
find the worksheet that corresponds to the codename in the other workbooks.

In a previous thread, you saw how I looped through the sheets in the workbook
and compared codenames to the name I needed. There are other ways, but I find
this the most reliable--I don't have to worry about any security setting that
other users may have.

 

[Dave Peterson]

Do you have any formulas in those cells that refer to that named range?

And you want the formulas in the receiving worksheet to use the defined name in
the new workbook, you can add:

application.displayalerts = false
'do the copy|paste
application.displayalerts = true

To avoid the prompt.

If you're working with names (and possibly external links)...

Here are some tools that'll help you:

Bill Manville's FindLink program:
http://www.oaltd.co.uk/MVP/Default.htm

And I like Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name
Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

Hi,

When I copy the cells of these pages, I get a message that one of my named
ranges is in the other book as well and askes me which to use. These sheets
being copied are not involved in this named range at all. They have no
formulas. The workbook has many named ranges but this is the only one
popping up. This named range is only used on one worksheet, CAMMaster.

Any ideas as to how I can stop this?

 

[Karen53]

Thak you, Dave!
--
Thanks for your help.
Karen53

Do you have any formulas in those cells that refer to that named range?

And you want the formulas in the receiving worksheet to use the defined name in
the new workbook, you can add:

application.displayalerts = false
'do the copy|paste
application.displayalerts = true

To avoid the prompt.

If you're working with names (and possibly external links)...

Here are some tools that'll help you:

Bill Manville's FindLink program:
http://www.oaltd.co.uk/MVP/Default.htm

And I like Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name
Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

 

[Buzz Local#30]

Hey I was just looking through this form and came across this thread it
intersted me as I was trying to think of a way to do something close to if
not the exact same as what you descussing here
Ok so I guess my question if no one objects to me asking is will that code
cope just the value of a formular meaning the end result? or will it copy a
formular also cause I could use something that would copy and add certain
colums of cells and certain cells either to a new workbook or the same
workbook in order to make excell apear to remember info