Copy PasteSpecial without hidden lines

**wb4syth** · 02-16-2011, 09:43 AM

I have done a fair amount of searching about this and have yet to find a working solution...

Goal – to programmatically copy spreadsheets (in Excel 2007) from one workbook and paste them into a new one. These sheets contain hidden rows which should not be in the new one.

Problem - The hidden rows are showing in the new one.

I am working with existing code - originally in Excel 2003.

Please Login or Register  to view this content.

___________________________________________
Attempted fix 1

Please Login or Register  to view this content.

The "Selection.Copy" line produces this error:
"Run-time error '1004':
This command cannot be used on multiple selections."
__________________________________________________
Attempted fix 2

Please Login or Register  to view this content.

The "Selection.PasteSpecial ..." line produces this error:
"Run-time error '1004':
Application-defined or Object-defined error"
Note: I tried several versions of the above one including copying it as a picture.
___________________________________________

I am fairly new to VBA coding so any help would be greatly appreciated.

Bill

**Colin Legg** · 02-16-2011, 09:49 AM

Hi Bill,

This quick test worked fine for me:

Please Login or Register  to view this content.

**wb4syth** · 02-16-2011, 10:57 AM

Colin - Thanks for your reply, I think it is getting me closer.

I didn't include all the code as it is lengthy but this workbook includes a number of sheets and loops through them.
I applied your suggestion to this and am now getting the sheets created however they are blank.

Here is the code (almost all of it - tried to strip out things that weren't pertinent) - you will see the stuff I added as "new code"...
NOTE: these sheets are protected - when I tried to unprotect them in the current workbook prior to "Set rngCopy = Sheets(n).UsedRange.SpecialCells(xlCellTypeVisible)" I get error "PasteSpecial method on Range class failed" on "rngTarget.PasteSpecial xlPasteValues"

Please Login or Register  to view this content.

I also tried using it closer to yours by not activating sheets before copy and pasting:
New Code:

Please Login or Register  to view this content.

**Colin Legg** · 02-16-2011, 11:14 AM

I've changed as little of your code as possible, but hopefully enough so that it 'works'. I've tried to remove most/all of your Select / Activate and used Workbook/Worksheet/Range variables to reference the correct objects.

Please Login or Register  to view this content.

**wb4syth** · 02-16-2011, 12:44 PM

Okay - so now I am wondering if there is some issue with my Excel 2007.
I created a new test workbook and reduced the code to its basics and still it pastes nothing in.

See attachment of the test sheet

Please Login or Register  to view this content.

**Colin Legg** · 02-16-2011, 12:48 PM

Try changing this line:

Please Login or Register  to view this content.

To this:

Please Login or Register  to view this content.

**wb4syth** · 02-16-2011, 01:28 PM

Colin - you've been a great help...

That worked for my test case.
But - I applied it to the original and the result is only one sheet showed up (and on every new one created the same data was pasted).

So the one that showed up was the only one that was not protected.

So I added this first line before setting the source:

Please Login or Register  to view this content.

And on the "rngSource.Copy" line I get "Cannot change part of a merged cell"...

**Colin Legg** · 02-16-2011, 01:40 PM

I would recommend that you unmerge all merged cells.

Catering for merged cells makes VBA more complicated than it needs to be. Formatting the cells to align by Centering Across Selection is the usual way to get the same visual effect as merged cells.

**wb4syth** · 02-16-2011, 02:11 PM

Well that works
Thanks for all your help and quick responses!

Of course this workbook has tons of formatting and liberal use of merged cells...so I have my work cut out for me.