How to select cell C1, copy paste then C2, copy paste then C3 etc

]Hi there. I am seeking some urgent advice from all of you VBA gurus…
*
I have a large spreadsheet written in excel but am struggling with my last macro.
*
I have done lots of formulas in this workbook but cannot get the last VBA to work properly.
*
I have tried Loop macros, Offset macros and ranges but cannot get it to copy next number down.
*
I have simplified the workbook (see below) and will change the code to suit the proper workbook.
*
Basically I need a macro, which looks down column “C” for a membership number, Copies the Membership number in C4 into Cell F4 (which then generates a specific data field) range H4:L46 Say.
*
Copy that data field into another workbook, (say Book2), renames the sheet with the membership number originally copied from (Book 1, Cell C4)
*
Then looks to see if Cell C5 has a membership number in. If it has it takes this number, Copies it then pastes it into Cell F4 again (which then updates the data field with new details)
*
Copies that data field into workbook (Book2) again into a new Sheet and renames that sheet with the copied membership number.
*
Basically I want it to run down column C and every time it has a number filled in, it copies that number and pastes it into Cell F4 (which generates my data field) then copy that data field into a new workbook (Book2) and renames the sheets respectively. And stops when there are no more new membership numbers.
*
And to finish it off…….. Say I get more members (which generates more membership numbers) or I make changes to one of the memberships already copied and run the macro again.
*
I would like it to UPDATE the original coped sheet in Book2 with the new data, rather than ignoring it because it already exists…
*
Any help would be appreciated…
*
Thanks again
*
Please see below for code

Please Login or Register  to view this content.