Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help Please
I have a worksheet with information extracted below:
Room # Time FirstName LastName 1 8:00 AM Bill Jones 1 8:00 AM Mark Brooks 1 8:00 AM Meg Finley 1 8:00 AM Mark Dawson 2 8:00 AM K Price 2 8:00 AM Roy Jones 2 8:00 AM Nan Smith 1 8:30 AM W Armacost 1 8:30 AM T Bush 1 8:30 AM B Stokes 2 8:30 AM R Payne 2 8:30 AM J Hunt 2 8:30 AM L Ferrol 1 9:00 AM Claude Moses 1 9:00 AM Mike Wit 1 9:00 AM Jim Johnson 2 9:00 AM Karen Domino 2 9:00 AM Lisa Russel 2 9:00 AM Tom Ingalls 2 9:00 AM M Toms I want to copy and paste to another section of the worksheet, say Range AA5 as below and is seeking to obtain a macro to do this. I also need to know how to end the macro when no additional data is found. 1 8:00 AM Bill Jones 1 8:00 AM Mark Brooks 1 8:00 AM Meg Finley 1 8:00 AM Mark Dawson 2 8:00 AM K Price 2 8:00 AM Roy Jones 2 8:00 AM Nan Smith 1 8:30 AM W Armacost 1 8:30 AM T Bush 1 8:30 AM B Stokes 2 8:30 AM R Payne 2 8:30 AM J Hunt 2 8:30 AM L Ferrol 1 9:00 AM Claude Moses 1 9:00 AM Mike Wit 1 9:00 AM Jim Johnson 2 9:00 AM Karen Domino 2 9:00 AM Lisa Russel 2 9:00 AM Tom Ingalls 2 9:00 AM M Toms The pasted data directly above is linked to a report. The original extract groups persons into groups of 3 or 4 depending the their assigned times. However, I need to loop through the original set of data, copy the first block and paste it at Range AA5, then copy the next block and paste at Range AA10, AA15, etc., as the report is linked to these cells. In other words, I need to keep the starting row in each block 5 rows above the next block, etc. The report would look like: Room # Time LastName LastName LastName LastName 1 8:00 AM Jones Brooks Finley Dawson 1 8:30 AM Armacost Bush Stokes 1 9:00 AM Moses Wit Johnson Room # Time LastName 2 8:00 AM Price Jones Smith 2 8:30 AM Payne Hunt Ferrol 2 9:00 AM Domino Russel Ingalls Toms No code would be required for the report as it is linked by formulas to the second extract above. Thanks in advance for any help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help Please
Phil
Here is a macro that does what I think you want. I'm also sending you a small file with this macro in it and the source data that I used for reference. HTH Otto Sub Rearrange() Dim Dest As Range Dim Source As Range Dim Start As Range Set Dest = [AA5] Set Start = [A2] Do Until Start.Row Range("A" & Rows.Count).End(xlUp).Row Set Source = Range(Start, Start.End(xlDown)) Source(1).Resize(Source.Count, 4).Copy Dest Set Dest = Dest.Offset(5) Set Start = Source(Source.Count).Offset(2) Loop End Sub "Phil" wrote in message ... I have a worksheet with information extracted below: Room # Time FirstName LastName 1 8:00 AM Bill Jones 1 8:00 AM Mark Brooks 1 8:00 AM Meg Finley 1 8:00 AM Mark Dawson 2 8:00 AM K Price 2 8:00 AM Roy Jones 2 8:00 AM Nan Smith 1 8:30 AM W Armacost 1 8:30 AM T Bush 1 8:30 AM B Stokes 2 8:30 AM R Payne 2 8:30 AM J Hunt 2 8:30 AM L Ferrol 1 9:00 AM Claude Moses 1 9:00 AM Mike Wit 1 9:00 AM Jim Johnson 2 9:00 AM Karen Domino 2 9:00 AM Lisa Russel 2 9:00 AM Tom Ingalls 2 9:00 AM M Toms I want to copy and paste to another section of the worksheet, say Range AA5 as below and is seeking to obtain a macro to do this. I also need to know how to end the macro when no additional data is found. 1 8:00 AM Bill Jones 1 8:00 AM Mark Brooks 1 8:00 AM Meg Finley 1 8:00 AM Mark Dawson 2 8:00 AM K Price 2 8:00 AM Roy Jones 2 8:00 AM Nan Smith 1 8:30 AM W Armacost 1 8:30 AM T Bush 1 8:30 AM B Stokes 2 8:30 AM R Payne 2 8:30 AM J Hunt 2 8:30 AM L Ferrol 1 9:00 AM Claude Moses 1 9:00 AM Mike Wit 1 9:00 AM Jim Johnson 2 9:00 AM Karen Domino 2 9:00 AM Lisa Russel 2 9:00 AM Tom Ingalls 2 9:00 AM M Toms The pasted data directly above is linked to a report. The original extract groups persons into groups of 3 or 4 depending the their assigned times. However, I need to loop through the original set of data, copy the first block and paste it at Range AA5, then copy the next block and paste at Range AA10, AA15, etc., as the report is linked to these cells. In other words, I need to keep the starting row in each block 5 rows above the next block, etc. The report would look like: Room # Time LastName LastName LastName LastName 1 8:00 AM Jones Brooks Finley Dawson 1 8:30 AM Armacost Bush Stokes 1 9:00 AM Moses Wit Johnson Room # Time LastName 2 8:00 AM Price Jones Smith 2 8:30 AM Payne Hunt Ferrol 2 9:00 AM Domino Russel Ingalls Toms No code would be required for the report as it is linked by formulas to the second extract above. Thanks in advance for any help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
need help to update macro to office 2007 macro enabled workbook | Excel Discussion (Misc queries) | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) |