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