Macro Help Please

[Guest]

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.

 

[Otto Moehrbach]

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

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.