Copying a worksheet and retaining values (not formulas)

  • Thread starter Thread starter Dante
  • Start date Start date
D

Dante

I have a comprehensive sales spreadsheet setup which
reads raw data from my PC, collates and calculates, and
displays a summary sheet.

This summary sheet is fine as long as it is on my PC, but
sending it to others who do not share the source data
means all the links are broken.

Rather than give access to the links, I want to create a
file containing the actual calculated values, layout and
formatting, for sending to others.

Can this be done? (and if so, how!)
 
Hi

1. way
Right-click on sheet tab, select 'Move or Copy', check 'Create a Copy' and
select 'New book' in 'To Book' field and press OK. You get an one-sheet new
workbook. Select the whole datarange on it and copy it. PasteSpecial.Values
into same range. Save the workbook It's all (maybe you get a dummy link
there - user can ignore it when opening, but there also was some tool for
removing such links mentioned in one of Excel NG's some time ago)

2. way
Select whole datarange on your original worksheet, and copy it. Create a new
workbook, select leftmost upper cell for wold-be datarange, and then
PasteSpecial.Values. When you want to preserve the look of your original
sheet, then without loosing the selection, do PasteSpecial.Formats and
PasteSpecial.ColumnWidths. Save the workbook. It's all (no remaining links
there)


Arvi Laanemets
 
Back
Top