| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chk sheet exits, copy all to another book.
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 -- Thanks for your help. Karen53 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Chk sheet exits, copy all to another book.
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 "Karen53" wrote: 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 -- Thanks for your help. Karen53 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Chk sheet exits, copy all to another book.
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 wrote: 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 -- Thanks for your help. Karen53 -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Chk sheet exits, copy all to another book.
Thank yo both for your help!
-- Thanks for your help. Karen53 "Dave Peterson" wrote: 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 wrote: 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 -- Thanks for your help. Karen53 -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Chk sheet exits, copy all to another book.
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 "Dave Peterson" wrote: 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 wrote: 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 -- Thanks for your help. Karen53 -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Chk sheet exits, copy all to another book.
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 Karen53 wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 -- Thanks for your help. Karen53 -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Chk sheet exits, copy all to another book.
Thak you, Dave!
-- Thanks for your help. Karen53 "Dave Peterson" wrote: 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 Karen53 wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 -- Thanks for your help. Karen53 -- Dave Peterson -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Chk sheet exits, copy all to another book.
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 "Joel" wrote in message ... 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 "Karen53" wrote: 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 -- Thanks for your help. Karen53 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can not copy a sheet to another book | Excel Worksheet Functions | |||
| Too Few Rows to copy sheet to another book | Excel Worksheet Functions | |||
| Copy a sheet to a new book unsuccessful | Excel Discussion (Misc queries) | |||
| create new book and copy sheet into.. | Excel Programming | |||
| copy printarea from more than 3 sheet & save in new book. | Excel Programming | |||
Linear Mode
