bconner
04-28-2010, 08:01 AM
I have a program below that will create a worksheet for each hospital in a report
What I would like to do next is copy and paste each Hospital record to the corresponding worksheet that was created....Can anyone help with this.....
Attached is an example report....
'Creates a worksheet for each Hospital
Dim rngstartP As Range
Dim rngendP As Range
Dim arySheets As Variant
Set rngendP = Range("F2:F" & Cells(Rows.Count, 8).End(xlUp).Row)
ReDim arySheets(1 To Worksheets.Count)
For ct = 1 To Worksheets.Count
arySheets(ct) = Worksheets(ct).Name
Next ct
For Each rngstartP In rngendP
If IsError(Application.Match(rngstartP.Value, arySheets, 0)) Then
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = rngstartP.Offset(1, 0).Value
ReDim Preserve arySheets(1 To UBound(arySheets) + 1)
arySheets(UBound(arySheets)) = rngstartP.Offset(1, 0).Value
End If
Next rngstartP
What I would like to do next is copy and paste each Hospital record to the corresponding worksheet that was created....Can anyone help with this.....
Attached is an example report....
'Creates a worksheet for each Hospital
Dim rngstartP As Range
Dim rngendP As Range
Dim arySheets As Variant
Set rngendP = Range("F2:F" & Cells(Rows.Count, 8).End(xlUp).Row)
ReDim arySheets(1 To Worksheets.Count)
For ct = 1 To Worksheets.Count
arySheets(ct) = Worksheets(ct).Name
Next ct
For Each rngstartP In rngendP
If IsError(Application.Match(rngstartP.Value, arySheets, 0)) Then
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = rngstartP.Offset(1, 0).Value
ReDim Preserve arySheets(1 To UBound(arySheets) + 1)
arySheets(UBound(arySheets)) = rngstartP.Offset(1, 0).Value
End If
Next rngstartP