Howto Copy/Paste Visible Only, Multi-Range, from Sheet1 to Sheet2

[Aaeijh]

Hi,
How do you acheive this in 1 vba macro at the same time:

1. copy/paste visible cells only from Sheet1 to Sheet2?
2. copy/paste multiple different ranges?

See Attachment.

[JLGWhiz]

It is unclear if you are attempting to copy only rows that are not hidden, or if you are trying to copy on rows where cells are not blank. Your example indicates that it is the latter, but you need to verify that.

[Aaeijh]

Clarification: only copy rows that are not hidden.
Thanks,

[Zer0Cool]

Take a look at this: https://msdn.microsoft.com/en-us/vba...s-method-excel

Specifically xlCellTypeVisible. You then treat it like any other range (ie: copy, paste, delete, etc)

[Aaeijh]

ok, after reading the microsoft article, I created this vba macro, but get an error:

Please Login or Register  to view this content.

Seems to be in the right direction, just cant pinpoint the source of the error,


See Attachment.

[JLGWhiz]

Well, yeah, you didn't copy, only paste. That will error every time.

Please Login or Register  to view this content.

[Aaeijh]

Thanks for pointing that out,

Here's the updated code, including the "Copy"

Please Login or Register  to view this content.

Unfortunately, its generating an error when running the macro.

• With Range("rGroup1TitleAndRows").Copy ---> ERROR: Method 'Range of Object' _Global Failed.
• With rGroup1TitleAndRows.Copy ---> ERROR: Runtime 1004, That command cannot be used on multiple selections.

Any additional thoughts on how to copy and paste a combination of ranges?

[JLGWhiz]

See if replacing this

Please Login or Register  to view this content.

With this

Please Login or Register  to view this content.

will work. I didn't test it.

[Aaeijh]

Once again, thanks for the reply,

Here's the new code:

Please Login or Register  to view this content.

But this error comes up:

macro-error.jpg


Let me know how I'm doing this wrong,?!,.


Thanks,

[daboho]

if not hidden cell

Please Login or Register  to view this content.

[Aaeijh]

daboho,

Thanks for this,!

The code works!, but it copy/paste every visible rows,

code-vs-desired-result.jpg

In addition to copy/paste only the visible rows, we're trying to select a specific combination of ranges...

ie - programattically only select what's in blue

sheet1.jpg

Any thoughts?

[daboho]

you talk that is hidden row
if hidden row not to copy using specialcells(12)

[Aaeijh]

Its a combination of both:

1. combine "specific ranges" from different rows that are not consecutive
2. only copy/paste visible rows

[JLGWhiz]

Once again, thanks for the reply,

Here's the new code:

Please Login or Register  to view this content.

But this error comes up:

Attachment 560914


Let me know how I'm doing this wrong,?!,.


Thanks,

Sorry about that, had a glitch when I posted and did not fix it properly. Here is replacement code.

Please Login or Register  to view this content.

[JLGWhiz]

Its a combination of both:

1. combine "specific ranges" from different rows that are not consecutive
2. only copy/paste visible rows

To use the SpecialCells(xlCellTypeVisible) function and get desired results, the rows you do not want to copy must be hidden, not blank and not camoflauged by changing the font to white.
VBA can still see when a range is not hidden even if the human eye sees it as empty.

[Aaeijh]

Hi JLGWhiz,

Slightly modified your code to make it work, but it doesn't achieve the desired outcome,

code-vs-desired-outcome2.jpg

Maybe its impossible to copy/paste from multiple range, idk,