Copy rows to specific ranges, depending on the contents of the row.
Posted by Lori D on January 14, 2002 1:45 PM
I have 2 sheets. Sheet2 contains a list of appointments & data. Column C contains the appointment time. I sorted the data by time and have 2 blank rows between each group of times.
I need to copy all of the data to another sheet, "Trailer Management Sheet", and I have set up 12 ranges in that sheet. Range "APPTS_1" contains about 15 blank rows, the next range is APPTS_2 with about 15 blank rows, and so on, up until 18:00.
What I want to do, but don't know how, is if column "C" on Sheet2 = 5:00, copy the rows and paste them into Range "APPTS_1" on the Trailer Management Sheet. Then go back and if column "C" contains 6:00, copy that and paste into Range "APPTS_2" of the Trailer sheet. Does anyone know how to get this done?
Posted by on January 14, 2002 10:18 PM
Is each copy area the same size as the range where it is going? (NT)
Posted by Lori on January 17, 2002 7:07 AM
Not necessarily. There will...
There will always be a different number of appointment times, though not more than 15 per hour.
Lori
Posted by Tom Dickinson on January 17, 2002 1:51 PM
Lori:
The following macro should meet your porposes. There are a couple of problems with the request, and that is that you can't copy a block of data to another block of a different size. I have written the program so that it will take a reading of the time in column C, and copy it at intervals of 15 rows. If times are skipped, the rows where it is copied to will be skipped also.
Eg. If you have 5:00, 6:00, and 7:00, they would start on rows 5, 20, and 35. If you only have 7:00, it would still start on row 35.
Before you start, you will need to check the name of the sheet where the data is to be copied (line 5 of the program), and start accordingly. When you start the program, you need to be in the sheet that has the data to be copied. You will be prompted for the row containing the first time slot, and also the row in the new sheet where you want 5:00 to be placed. The first time in each group needs to be on the hour (e.g., 8:00, not 8:05). The macro will then search for the 2 blanks in column C, and copy the rows to the new sheet. It will keep looking for the time groups and copy them until it finds 3 blanks in column C.
Towards the end there is a long row:
Range("A" & (Format(Val(Range(Strng & "!C" & StRw)) / 0.041667, 0) - 5) * 15 + NwRw).Select
If the time is to start at other than 5:00, then switch the "- 5)" to the hour at which it starts.
One last thing: the calculations are based on a 24 hour clock (which is what your request seemed to indicate). If the first time listed in each group is not in that format, there will be writes overs and erased data.
Good luck.
Sub TralrSched()
Dim StRw, NdRw, NwRw As Integer
Dim Strng1, Strng2 As String
Strng1 = ActiveSheet.Name
Strng2 = "Trailer Management"
Range("A1") = Strng
NdRw = InputBox("What is the first row containig a time in column C?")
NwRw = InputBox("What row does the 5AM data go to?")
Do Until Range("C" & NdRw) = Empty And Range("C" & NdRw).Offset(1) = Empty _
And Range("C" & NdRw).Offset(2) = Empty
StRw = NdRw
Do Until Range("C" & NdRw).Offset(1) = Empty And Range("C" & NdRw).Offset(2) = Empty
NdRw = NdRw + 1
Loop
Rows(StRw & ":" & NdRw).Copy
Sheets(Strng2).Select
Range("A" & (Format(Val(Range(Strng & "!C" & StRw)) / 0.041667, 0) - 5) * 15 + NwRw).Select
ActiveSheet.Paste
Sheets(Strng1).Activate
NdRw = NdRw + 3
Loop
Application.CutCopyMode = False
Sheets(Strng2).Select
End Sub
