Retain Format When Coping Visible Cells Only to New Worksheet
 

I have a spreadsheet with around 5000 rows and 100 columns. I hide
rows and columns, do some filtering and subtotaling when creating
different reports. I would like to be able to create a report, then
copy only the visible cells and columns and paste theses to a new
worksheet while maintaining the format. I can't get it to work. It
seems the problem is since I'm not pasting the data to the same
columns the formatting gets all messed up. Format Painter doesn't
help. I have a macro that deletes all hidden rows and columns and it
does maintain the formatting but it's too slow. Does anyone know of
a way to do copy Visible Cells Only while maintaining the same format?

Retain Format When Coping Visible Cells Only to New Worksheet
 

I put some test data in a worksheet, I applied data|filter autofilter, I added
data|subtotals, I formatted a few cells (fill/font/bold).

I hid some columns. I hid some rows using the outlining symbol to the left.

I selected the range to copy.
I hit Edit|Goto|Special|Visible cells only

I pasted to a new sheet and both the values and formatting were pasted.

The columnwidth's weren't pasted, though. But if you're using xl2k+, you could
do edit|paste special|columnwidths to get that.

I used xl2003 in my test. Does this work differently for you?

If so, what version of excel are you using?

Ronster wrote:

I have a spreadsheet with around 5000 rows and 100 columns. I hide
rows and columns, do some filtering and subtotaling when creating
different reports. I would like to be able to create a report, then
copy only the visible cells and columns and paste theses to a new
worksheet while maintaining the format. I can't get it to work. It
seems the problem is since I'm not pasting the data to the same
columns the formatting gets all messed up. Format Painter doesn't
help. I have a macro that deletes all hidden rows and columns and it
does maintain the formatting but it's too slow. Does anyone know of
a way to do copy Visible Cells Only while maintaining the same format?

--

Dave Peterson

Retain Format When Coping Visible Cells Only to New Worksheet
 

I'm using xl2K and it doesn't work the same as yours. When I copy
Visible Cells Only and paste them to a new WS ALL the columns and rows
are the same and about 1/3 of the data is unreadable (requires
resizing). I tried the edit|paste special|columnwidths but when
pasting I get an error message saying the areas aren't the same size.
It looks like it's time to upgrade to 2003 or use my slow delete hidden
rows/columns macro.

Retain Format When Coping Visible Cells Only to New Worksheet
 

Or just copy the columnwidths separately--or select the whole column(s) first
(untested).

Ronster wrote:

I'm using xl2K and it doesn't work the same as yours. When I copy
Visible Cells Only and paste them to a new WS ALL the columns and rows
are the same and about 1/3 of the data is unreadable (requires
resizing). I tried the edit|paste special|columnwidths but when
pasting I get an error message saying the areas aren't the same size.
It looks like it's time to upgrade to 2003 or use my slow delete hidden
rows/columns macro.

--

Dave Peterson

Retain Format When Coping Visible Cells Only to New Worksheet
 

The Paste Special ColumnWidths worked! First I create the report with
all the hidden rows and columns with the formatting just the way I want
it. I then create a new worksheet. After that I do the Edit, GoTo,
Special, Visible Cells Only. I then switch to the new WS, location A1.
I do the Paste Special Column Widths which reformats ALL the columns
to the same size as the origianl. I then simple do a Paste which
inserts all the visible data and any colored cells or other formatting
that the other WS had. Works great! Thanks

Retain Format When Coping Visible Cells Only to New Worksheet
 

Glad you got it working.

Ronster wrote:

The Paste Special ColumnWidths worked! First I create the report with
all the hidden rows and columns with the formatting just the way I want
it. I then create a new worksheet. After that I do the Edit, GoTo,
Special, Visible Cells Only. I then switch to the new WS, location A1.
I do the Paste Special Column Widths which reformats ALL the columns
to the same size as the origianl. I then simple do a Paste which
inserts all the visible data and any colored cells or other formatting
that the other WS had. Works great! Thanks

--

Dave Peterson