Copying Groups of Cells in Succession

[milagros]

Hello,

I'm trying to create a macro that copies a certain amounts of cells (say 500) in one column and then waits for a keypress to copy the next 500, and continues to do this until the end of the column is reached whereby the macro is terminated.

When initially running the macro I'd like to be able to define in the start the number of cells to copy rather than editing the macro in case I want to change the number from 500.

Below is a more detailed order of what the macro has to do and things that are needed for it to do this.

Macro start position is "current cell"

1. Prompt for number of cells to copy and place # into variable (copyCount)
2. Copy cells from current cell to cell "copyCount" rows down. (i.e A1 - A500)
3. If data count < copyCount (i.e. A490 is last data row where cell is not blank)
Terminate macro
4. If data count = county count, (both are 500) then pause and prompt
(Copy more Yes / No)
5. If no - terminate macro
6. If yes - repeat steps 3 through 5

I had a ton of trouble just getting to Step #2 :-), so I definitely would appreciate any help you can offer!

[StephenR]

Please post a sample workbook.

[milagros]

Thanks for posting Stephen :-)

I've added an attachement dataset, along with a rehash of how the the macro would copy the data within. please let me know if you need more info.

I'm using the data in external programs outside of excel, so I need the macro to pause between copies, to give me time to paste the data into the other programs.

[StephenR]

Not sure where you are pasting, so don't know if the copying bit will work. If you assign this to a button you can just click it every time you need to copy code.

Please Login or Register  to view this content.

[milagros]

Thanks for your reply Stephen, much appreciated!

I added a button to the sheet as you mentioned, but still need some help as there are still a bunch of things that are missing in the macro that I don't know how to do.

I would like the copy to go the clipboard, and not "delete" the info that was copied (so I remove your delete function).

The button now copies the first 500 cells correctly, but the "Copy More?" prompt loop is missing (4 datasets in example / 1801 cells total) as well an auto-termination when data ends (at 1802).

The data copy is also set to start at A1 (now A2 to accomodate button), but I need this value to be taken from the "current cell" as the data when I run the macro will not always be in A1 or A2.

The current cells is also needed to gather the next dataset.. as in copy CurrentCell + nsize = next current cell.

Let me know if this makes what I need more clear. There is further clarifcation in column B of the worksheet, that gives the steps of how the macro would copy all datasets in worksheet.