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?

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

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.

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

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

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