Macro to copy and paste Ranges

[djmarsh51]

Hello,

I have a spreadsheet which has 2000+ (but could be more) rows only some of which I want to copy and paste to a new spreadsheet. Data goes from Column A to I. I would like a marco to start at A29 (copy the range from A29:I36 - 10 rows x 9 columns) copy that and paste on a new sheet (Sheet 1). I would then like the Macro to go back to A29, and go to the next non-blank cell, and copy 10 rows x 9 columns and paste in on the previous "Sheet 1" below the previous data. I would like it to continue this process until the Macro can no longer find any non blank cells in column A to copy the range.

Thanks for the help!

[rvasquez]

Hello there,

Can you post a sample workbook that mimics the results you would like from the macro? I believe I understand what you are looking for but it seems that you would be duplicating data so I just want to make sure.

Thanks!

[djmarsh51]

Hi rvasquez,

Here is an example of what I am looking for.

First tab is the input, and I made a slight error in my previous post. I would like the macro to start at A149 or "Ball" copy a range of 10 rows X 9 Columns, and paste it on a new sheet (output report in my example) then go back to A149 and look for the next non-black cell in Column A (in this case cell A291 or "Dog") and once again copy the 10 rows X 9 columns and paste it on the output report after the previous data, and continue this process for until it no long finds a non-blank cell in column A (eg in my sample after copying and pasting the block starting at A575, there is no more non-blank cells in column A so that would be the last one). All data with XXXXXXXX or blank and not in the 10X9 blocks is not to be copied to the new sheet.

Hope this makes it more clear. I attached an example. Thanks again.

[rvasquez]

Hello there,

Try the below code:

Please Login or Register  to view this content.

To insert the code into your workbook

1. Press Alt+F8
2. Clear the macro name box and the type FormatOut
3. Select the Create option
4. In between the Sub FormatOut() and End Sub copy and paste the above code
5. Anything that appears in green is a comment to help you understand the code
6. Close out of Visual Basic window
7. Press Alt+F8 again
8. This time select the FormatOut macro and select run

Let me know if this works for you!

Thanks!

[djmarsh51]

Hi rvasquez

Works pefect. Thanks for you help and explantions in in the coding.

[rvasquez]

No problem!