Can this be done?
Posted by Lori on January 11, 2002 12:21 PM
I have to re-format a report from our system into Excel. The data is grouped by appointment times, with 2 blank rows at each change in appointment time. The sheet I need to copy the data groups to is set up in ranges; 1 for each appointment slot. Ex: APPTS_1, APPTS_2, etc. How can I have excel copy the rows containing 5:00's to the 1st range on the other sheet, 6:00's to the next range on the other sheet, and so on until all appointmets have been copied? Thanks in advance. You are always most helpful!
Posted by Jacob on January 11, 2002 1:56 PM
Hi
Assuming the times are in column A and everything is on "Sheet1" and you want to move it to APPTS_1 and APPTS_2
Sub OrganizeIt()
Dim LastRow as Integer
Dim x as Integer
application.screenupdating = false
LastRow = Sheets("Sheet1").Range("A65536").end(xlup).row
for x = 1 to lastrow
Sheets("Sheet1").activate
Select Case Range("A" & x).value
case 5:00
range("A" & x).copy
sheets("APPTS_1").avtivate
range("A65536").end(xlup).offset(1,0).select
activesheet.paste
case 6:00
range("A" & x).copy
sheets("APPTS_2").avtivate
range("A65536").end(xlup).offset(1,0).select
activesheet.paste
case else
end select
next x
Sheets("APPTS_1").range("A1").select
Sheets("APPTS_2").range("A1").select
Sheets("Sheet1").delete
application.cutcopymode = false
End Sub
HTH
Jacob
Posted by Lori on January 14, 2002 7:27 AM
Jacob, I didn't explain well...
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.
I tried to adjust the code you sent me, but I can't figure out how. If you can still help me with this, thanks, and if not thanks anyway!
