can only copy/select visible cells w autofilter on &hidden columns, want to copy all cells

It is only copying/selecting the visible cells. I want to copy the entire range, hidden and visible cells alike or select the entire range.

My worksheet has autofilter on and hidden columns & I have the following vba macro that inserts rows then copies the range "workpack" and pastes it into the newly inserted rows and a macro that deletes all values on the etc forecast line

However, if any filters are applied, when the macro is executed it doesn't behave as i want it to. Instead of copying the entire range like i want, it only copies the visible cells and pastes it in as values.

Please see the attached workbook. Execute the code by either clicking the button or hitting ctrl + shift + z. You will see what i mean. Set the filter so no filters are applied and then try running the macro again & it works properly as intended

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I tried

Please Login or Register  to view this content.

but it still only copied visible cells

I am trying to avoid turning off autofilter as that triggers a recalc on my main sheet and the user has to reapply their filters and i am trying to avoid unhiding columns

So how can i get excel to copy/select the entire range or all cells in the defined range, hidden and visible.

is there some code similar to below that will do what I want

Please Login or Register  to view this content.

EDIT: MARKING THIS AS SOLVED

See this thread for solution to this problem: http://www.excelforum.com/excel-prog...09#post2802409




thanks

Unfortunately I need everything copied down to the newly inserted cells exactly as it is in the range(There won't be any values in the formula holding range on my my spreadsheet, it will all be formulas, except for a blank etc forecast line). In my working spreadsheet the top workpackage will be left blank as a formula holding range. There are index/match formulas on the bcws, bcwp, act, & etc lines that pull that data from a raw data sheet for a particular work package when a user enters a lookup value in the cell.

As new raw data is pasted in, the workpackages update with the latest data.

My spreadsheet can have anywhere from 1 to hundreds of workpackages which is why i'm trying to avoid having to force auto filter off and making the user reset the autofilter after

Thanks, for the input. That looks like it loops through each cell individually and then copies it down, pretty slick. I'm amazed that microsoft doesn't have a cleaner way of doing this or that it defaults to this behavior. I mean they have the "xlCellTypeVisible" command to select only visible cells and the F5>Go To special>Visible cells if that's what you want so why not an "all type" and why default to visible cells only. Doesn't make sense from a recalculation/optimization stand point.

I did notice that if you manually insert the rows, then copy the range, then paste it (pastes it in all messed up), but then hit undo, then hit paste again, it re-pastes it but this time it will re-paste it properly with all cells, hidden and visible.

It's like excel realizes when you hit undo and repaste that you want all cells not just visible cells. And you can see this evidenced by that when you hit undo the moving copy border changes to be copying the entire range with out the double lined copy border at the points where the hidden columns are. So when you hit undo excel changes the copy range from visible cells only to all cells. And then when you paste it pastes it in right

Any idea of the underlying behavior that's going on here or is there any way to get insight into this code or what's going on in the background? I tried to do this with the macro recorder to find out what excel was doing but the recorder didn't record the other actions and only recorded it as if i hadn't hit undo.

Excel copy whether manually or using code will only copy visible cells.

What do you need to do exactly because the code you posted is mostly unnecessary