copying one worksheet to another workbook

Topic

New Reply

I am using Excel 2007 & Windows 7.

I have a ‘master workbook’ that I frequently make some changes to and I use it like a template; that is, I open the master and use it then save it under a different name so it is just like the master but with a different name. In this master workbook, I use many named ranges – some a block of cells and some a single cell. However, I have one worksheet in this workbook that contains none of these named ranges. When I want to copy this worksheet to another workbook that I have previously saved (which began as the master workbook and was saved to a different file name), I receive a lot of error notices that the workbook to which I am copying the worksheet already contains the named values. I am presented with the option of renaming the values in my worksheet. In fact, i receive such an error notice for each of the many named ranges (values) in my workbook.

Is there any way to avoid this? Seems that since the worksheet I want to copy contains nothing but formulas with no reference to a named value, this should not be a problem.

Reply | Quote

Replies

Hi Don,

I think you can get around this via Edit|Paste Special > Formulas. If there’s cell formatting applied to the range copied from, you might need to couple that with Edit|Paste Special > Formats.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Named formulas/ranges are part of the workbook and not part of an individual worksheet and thus are in some ways part of EVERY worksheet. This is what allows their use in cond formatting, validation, etc when cells in the same sheet are required since they act like they are on that sheet.

Unfortunately, you can get the problem that when a sheet is copied from one workbook to another all the names from the source workbook are all copied into the destination workbook. If the names already exist in the destination, you have to tell EXCEL what to do about each name.

If the names have nothing to do with the sheet, you could try creating a new worksheet in the destination and copy all the CELLS from the source worksheet into the new empty destination worksheet.

Another option would be to copy the source worksheet to a intermediate new blank workbook, and then delete all the names in this workbook (you could do it with a macro, but it is just as quick to run the single line (actually 3 LOCs) from the immediate window:
for each nm in activeworkbook.Names:nm.delete:next

Then you can copy this worksheet to the destination workbook.

Steve

Reply | Quote

Thanks for the help. The work-around I have been using has been to click the top left of the worksheet (selecting the entire sheet) then copy/paste into the desired workbook. I will do the intermediate workbook as suggested.

Reply | Quote