Tweak my code so it does not copy rows that are in the shaded area

[Roco]

Here is my question and what I would like to do. Please see attached. I would like to copy all rows accept for the ones that are in the shaded area. (Copy Only...I will not paste this any place back into excel. They will not always fall in the same row area. I want to tweak my "Copy Rows" button, so it does not copy rows that are "Shaded". My Shaded items will alway fall between "General Conditions and "SubTotal General Conditions". Also Shaded items will fall between "Surveying and SubTotal Surveying".

Sometimes I will have more or less items that are shaded, and they will not alway fall in the same rows, but will always fall between what I have stated above. Can this be done? If so I would really appreciate the help in adding to the marco. Please see attached.

[Winon]

Hello Roco,

Try to replace your entire Code, with the Code below;

Please Login or Register  to view this content.

Regards.

[Roco]

Thanks you for your reply. I see and understand what it is doing with the Autofilter. This is the error that I get showing up in yellow.

[Range("B2:E2", Cells(Rows.CountLarge, "B").End(xlUp).Offset(, 2)).Copy Sheet1.Range("B2")]

[Winon]

Thank you for the feedback Roco,

Unfortunately I cannot replicate your problem.

Please try the attached Workbook, and if the problem persists on your side we would have to try something else, since it works fine on my side.

Regards.

[Roco]

It worked up to the auto filter and did not copy. I'll download your example in the morning. Thanks so much for working on this. I'll let you know something tomorrow. Roco

[Winon]

Hi Roco,

In the meantime, I have tested the sample workbook again,And it works without fail on my side. to make it easier for you, I have even added Buttons to the respective Sheets to make the testing of same easier for you. It is worth at least one Zimbabwean dollar! More or less worth 5 Pennies in you currency. LOL. So you better pay up, to avoid legal proceedings, if only it was worth the effort and paperwork.LOL

Regards.

[Roco]

Winon,

Thanks for the attached....it works, but not what I'm looking for. I do not want it to paste anywhere in excel. I just want to be able to copy the rows that are not shaded, (clip board) so I can paste that information into another software program. Is that possible. I had no idea that a an Zimbabwean dollar was worth so much. For the effort that you have put into this, 5 Pennies is well worth it! I could possible afford 2 Zimbabwean dollars.

[Winon]

Hi Roco,

It seems that the Clipboard cannot hold a filtered Range. It appears to ignore filters, and only hold the complete Range as if it is not filtered.

The only workaround I could come up with, was to Create a Sheet1, and make it invisible. The Filtered Range then gets copied to the hidden Sheet1, and the Code then Copies the Data in Sheet1, from where you can then Paste it to where you want. MSWord, NoteBook, or where you like. There is only one caveat when you paste it to another Excel Sheet. If you do that, it responds but without adjusting Column Width. All you need to do then is to Click on Paste Special, and select Column Widths, Done.

The sample attached Workbook as below will show you how it works.

Hope that helps, cause I am tempted to take you on with your offer.

For the effort that you have put into this, 5 Pennies is well worth it! I could possible afford 2 Zimbabwean dollars.

Regards.

[Winon]

Hello Roco,

Thank you for adding to my Reputation

Please donate the 2 Zimbabwean dollars, to a charity of your choice, and disappear into thin air faster than "Flash".LOL.

Thanks.