Copy and paste from one sheet to another leaving out hidden cells

[si_1970]

Hi all,

First time poster and a novice at using vba but learning quickly. The more I learn the more impressed I am by how vba can make life so much easier if you have to constantly update data in spreadsheets...............which my employer loves (why bother asking someone if they take suger in their coffee when you can create a spreadsheet that contains everyones sugar requirements and then tell some poor sod to keep it updated).

I am trying to use a vba code to copy data from various cells on one worksheet (leaving out hidden cells) and then paste the data into every seventh row on a second worksheet (in the same workbook). I have used an editing vba code found on this site to copy and paste the data as required but the code is also copying data from hidden rows.


The code that i am using is......

Please Login or Register  to view this content.

Thanks in advance for any help

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[si_1970]

Thanks John,

It didn't work at first but if i change - If ws2.Rows(j).Hidden = False Then to If ws1.Rows(i).Hidden = False Then it works but where there was a hidden row it pastes the data every 14 rows instead of every 7 so i end up with big gaps.

[JOHN H. DAVIS]

Not sure how you're i variable is working. Can you post a copy/sample of your workbook?

To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

[AB33]

It appears to be a mix-up of the data.

Please Login or Register  to view this content.

Looks at the last row in sheet1, but the code loops through and looking for values in sheet 2

[si_1970]

I have attached a amall example of what my sheet looks like. Column H on the ExDRIE spreadsheet is filtered to hide all rows with a Y. I want to convert the remaining data to a more user friendly print page listing the required data down the page with a single line space between each one like so -

Account #
Customer Name
Addr
Addr 2
Post Code


Example.xlsm

The page will then be formatted to print approx 10 per page. I know that my column numbering is out for the information that I want to copy across to the print page but I was going to correct that once i had the vba working correctly.

[JOHN H. DAVIS]

I'm still confused. Do you want too move data from Exdrie (from the specified column headers in Post#6) if the rows are not hidden? Create a List on the List Sheet? Down the Column or Accross the row?

[si_1970]

I want to copy certain parts od the data that is not hidden from the ExDrie sheet and paste it Down the Columns on the List sheet.

[AB33]

Why are you jumping on every 7 rows when you copy the unhidden rows, why not the next empty row?

[JOHN H. DAVIS]

I want to copy certain parts od the data that is not hidden from the ExDrie sheet and paste it Down the Columns on the List sheet.

Are the certain parts you want those you list in Post #6? And do you want the information posted by down or accross on the list sheet?

[JOHN H. DAVIS]

Does this do what you want?

Please Login or Register  to view this content.

[AB33]

It would have been easier to filter a column by criteria and copy only the visible cells. This avoids for testing a hidden cell and no need to loop either.

[JOHN H. DAVIS]

Normally when I filter and paste the visible cells, I do it as one group to paste to one cell. How would you paste each line to every 7th row.

[AB33]

John,
every 7th row is the pasting area, so when we paste we increase the next row by 7.

[JOHN H. DAVIS]

Can your provide a code sample of the above request using the filter method without using a loop and pasting to every 7th row.