Copying dynamically from one sheet to another without blanks

**fynn** · 06-05-2008, 07:32 AM

I hope this one hasn't been done to death, well I know it has. But mine is a bit different.

I have a master sheet that contains a macro. I would like it to jump to a selected sheet, select the range A13 to the last row of C column that contains data. This would then be copied and pasted into the master sheet starting at C3, without blanks.

I came very close just now but got stuck in an endless loop and lost my work

I remember that it would paste fine but the skipblanks property of pastespecial did nothing.

Can anyone help with this?

**royUK** · 06-05-2008, 08:11 AM

Why do you need SkipoBlanks, if you set the range to copy within the code it won't pick up blanks, how do you intend selecting a sheet?

Something like this:

Please Login or Register  to view this content.

**fynn** · 06-06-2008, 12:52 AM

Thanks for the reply. I need to skip the blank cells as there are about 50-100 between each meaningful entry and the summary page (where the entries are to be dumped) needs to be readable. Sheet selection, I would love it to cycle through the data entry pages (which are all in a block of sheets, one after the other) and repeat the copy paste operation. It should be easy because the data starts in exactly the same place on each sheet.

Will try the above and post back!

**fynn** · 06-06-2008, 01:16 AM

OK, the above code copies the correct range of data but pasting it onto the new sheet leaves all the spaces, formatting and some buttons I have there. So it's still including blanks... Never mind, just recorded a simple macro to use goto special blanks and delete with **** cells up. Code isn't pretty but it works I guess!