Getting Sheets Copied From One Workbook to Another Without ....? Mhz <Mhz.2b0lx7_1153014312.233[ at ]excelforum-nospam.com> 16.07.2006 01:41:59 Hello, I have a situation where I want to move 3 sheets from one workbook to another. The Problem is that the sheets appears to carry their File Path with them creating a Problem for my formulas within the destination sheet.. Is there a way to keep the Formulas in tact to represent the destination sheet? The Workbooks have the same Data, but 3 sheets from the source workbook need to be inserted in the destination workbook without paths in the formulas leading back to the source workbook (file). eg. Source workbook sheet1 A1 Reads: =IF(DAY14!B33>0,DAY14!C33,"") When it is copied to the Destination Worksheet it Reads: =IF('[JULY CALLS CREATION BU9 TESTING.xls]DAY14!B33>0,'[JULY CALLS CREATION BU9 TESTING.xls]DAY14!C33,"") I modified the above formula slightly to remove the Reference errors, but I just want to show basically what is happening... Thanks for any help here... -- Mhz ------------------------------------------------------------------------ Mhz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35980 View this thread: http://www.excelforum.com/showthread.php?threadid=561798

Re: Getting Sheets Copied From One Workbook to Another Without ....? Mallycat <Mallycat.2b0nrw_1153016712.0789[ at ]excelforum-nospam.com> 16.07.2006 02:22:58 Not sure what happens with the source spreadsheet when you export the 3 sheets. Depending on this, you may be able to use SAVE AS, then delete the sheets you don't want, rather than try to export the ones you want to keep. This should solve the problem, but it will depend on what you want to do with the source sheet after the export. Matt -- Mallycat ------------------------------------------------------------------------ Mallycat's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35514 View this thread: http://www.excelforum.com/showthread.php?threadid=561798

Re: Getting Sheets Copied From One Workbook to Another Without ....? Max 17.07.2006 09:25:02 Some thoughts .. [Quoted Text] > .. The Problem is that the sheets appears to carry > their File Path with them creating a Problem > for my formulas within the destination sheet [book] Think it's because the formulas on the sheets within the source book to be moved over are referencing other sheets in the source book > Is there a way to keep the Formulas intact to represent the > destination sheet? Try tinkering in this way .. First, ensure that all dependent sheetnames referenced by the formulas within the sheets to be moved over, eg: Day14, etc actually *exist* within the destination book before proceeding .. In the source book, (Do this one sheet at a time) Select the entire sheet to be moved Click Edit > Replace Find what: = Replace with: zzzzz Click Replace All (this effectively "neutralizes" all formulas on the sheet) Then move the sheet over to the destination book, and reverse the process .. Select the entire sheet (which is copied over) Click Edit > Replace Find what: zzzzz Replace with: = Click Replace All (Array formulas, if any, would need to be re-entered using CSE & re-filled) Repeat for next sheet ... -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Mhz" wrote: > Hello, I have a situation where I want to move 3 sheets from one > workbook to another. The Problem is that the sheets appears to carry > their File Path with them creating a Problem for my formulas within the > destination sheet.. > > Is there a way to keep the Formulas in tact to represent the > destination sheet? > > The Workbooks have the same Data, but 3 sheets from the source workbook > need to be inserted in the destination workbook without paths in the > formulas leading back to the source workbook (file). > > eg. > > Source workbook sheet1 A1 Reads: > > =IF(DAY14!B33>0,DAY14!C33,"") > > When it is copied to the Destination Worksheet it Reads: > > =IF('[JULY CALLS CREATION BU9 TESTING.xls]DAY14!B33>0,'[JULY CALLS > CREATION BU9 TESTING.xls]DAY14!C33,"") > > I modified the above formula slightly to remove the Reference errors, > but I just want to show basically what is happening... > > Thanks for any help here... > > > -- > Mhz > ------------------------------------------------------------------------ > Mhz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35980 > View this thread: http://www.excelforum.com/showthread.php?threadid=561798 > >

Re: Getting Sheets Copied From One Workbook to Another Without ....? Max 17.07.2006 09:26:01 Some thoughts .. [Quoted Text] > .. The Problem is that the sheets appears to carry > their File Path with them creating a Problem > for my formulas within the destination sheet [book] Think it's because the formulas on the sheets within the source book to be moved over are referencing other sheets in the source book > Is there a way to keep the Formulas intact to represent the > destination sheet? Try tinkering in this way .. First, ensure that all dependent sheetnames referenced by the formulas within the sheets to be moved over, eg: Day14, etc actually *exist* within the destination book before proceeding .. In the source book, (Do this one sheet at a time) Select the entire sheet to be moved Click Edit > Replace Find what: = Replace with: zzzzz Click Replace All (this effectively "neutralizes" all formulas on the sheet) Then move the sheet over to the destination book, and reverse the process .. Select the entire sheet (which is copied over) Click Edit > Replace Find what: zzzzz Replace with: = Click Replace All (Array formulas, if any, would need to be re-entered using CSE & re-filled) Repeat for next sheet ... -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Mhz" wrote: > Hello, I have a situation where I want to move 3 sheets from one > workbook to another. The Problem is that the sheets appears to carry > their File Path with them creating a Problem for my formulas within the > destination sheet.. > > Is there a way to keep the Formulas in tact to represent the > destination sheet? > > The Workbooks have the same Data, but 3 sheets from the source workbook > need to be inserted in the destination workbook without paths in the > formulas leading back to the source workbook (file). > > eg. > > Source workbook sheet1 A1 Reads: > > =IF(DAY14!B33>0,DAY14!C33,"") > > When it is copied to the Destination Worksheet it Reads: > > =IF('[JULY CALLS CREATION BU9 TESTING.xls]DAY14!B33>0,'[JULY CALLS > CREATION BU9 TESTING.xls]DAY14!C33,"") > > I modified the above formula slightly to remove the Reference errors, > but I just want to show basically what is happening... > > Thanks for any help here... > > > -- > Mhz > ------------------------------------------------------------------------ > Mhz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35980 > View this thread: http://www.excelforum.com/showthread.php?threadid=561798 > >

Re: Getting Sheets Copied From One Workbook to Another Without ....? Max 17.07.2006 09:29:01 Some thoughts .. [Quoted Text] > .. The Problem is that the sheets appears to carry > their File Path with them creating a Problem > for my formulas within the destination sheet [book] Think it's because the formulas on the sheets within the source book to be moved over are referencing other sheets in the source book > Is there a way to keep the Formulas intact to represent the > destination sheet? Try tinkering in this way .. First, ensure that all dependent sheetnames referenced by the formulas within the sheets to be moved over, eg: Day14, etc actually *exist* within the destination book before proceeding .. In the source book, (Do this one sheet at a time) Select the entire sheet to be moved Click Edit > Replace Find what: = Replace with: zzzzz Click Replace All (this effectively "neutralizes" all formulas on the sheet) Then move the sheet over to the destination book, and reverse the process .. Select the entire sheet (which is copied over) Click Edit > Replace Find what: zzzzz Replace with: = Click Replace All (Array formulas, if any, would need to be re-entered using CSE & re-filled) Repeat for next sheet ... -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Mhz" wrote: > Hello, I have a situation where I want to move 3 sheets from one > workbook to another. The Problem is that the sheets appears to carry > their File Path with them creating a Problem for my formulas within the > destination sheet.. > > Is there a way to keep the Formulas in tact to represent the > destination sheet? > > The Workbooks have the same Data, but 3 sheets from the source workbook > need to be inserted in the destination workbook without paths in the > formulas leading back to the source workbook (file). > > eg. > > Source workbook sheet1 A1 Reads: > > =IF(DAY14!B33>0,DAY14!C33,"") > > When it is copied to the Destination Worksheet it Reads: > > =IF('[JULY CALLS CREATION BU9 TESTING.xls]DAY14!B33>0,'[JULY CALLS > CREATION BU9 TESTING.xls]DAY14!C33,"") > > I modified the above formula slightly to remove the Reference errors, > but I just want to show basically what is happening... > > Thanks for any help here... > > > -- > Mhz > ------------------------------------------------------------------------ > Mhz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35980 > View this thread: http://www.excelforum.com/showthread.php?threadid=561798 > >

Re: Getting Sheets Copied From One Workbook to Another Without ....? Mhz <Mhz.2b3phv_1153159008.1897[ at ]excelforum-nospam.com> 17.07.2006 17:53:13 Oh Max, I would be Nights Behind if I didn't have your powerful programming help ;) YES, this works great for moving Sheets between Workbooks without the File Link Traveling Behind ... Thanks Very Much ..... The Frown Has returned An Happy Face ...:) -- Mhz ------------------------------------------------------------------------ Mhz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35980 View this thread: http://www.excelforum.com/showthread.php?threadid=561798

Re: Getting Sheets Copied From One Workbook to Another Without ... Max 18.07.2006 03:39:01 "Mhz" wrote: [Quoted Text] > Oh Max, I would be Nights Behind if I didn't have your powerful > programming help ;) YES, this works great for moving Sheets between > Workbooks without the File Link Traveling Behind ... Thanks Very Much > ..... The Frown Has returned An Happy Face ...:) Glad to hear it worked ! Like you now <g>, I've also learnt from and benefited immeasurably from the many regular responders in these excel newsgroups over the years. Besides reading answered posts, try plunging in and answering some posts too. That's one good way to enjoy and learn around here. -- Max Singapore http://savefile.com/projects/236895 xdemechanik ---