bconner
04-27-2010, 11:39 AM
I have a report and I want to parse the data into seperate Worksheets By Hospital Name. Since the Hospital Name can exist multiple times in the data I need to loop thru the worksheets before creating a new worksheet to make sure the name doesn't already exist.
I can't Sort the file to group all hospital names together it has to stay in the order it is in....
I am attaching some sample data
Below is the code I am trying to use to Loop thru the data and create one worksheet for each Hospital Name:
Dim rngstartP As Range
Dim rngendP As Range
Dim BExists As Boolean
BExists = False
For ct = 1 To Worksheets.Count
If rngstartP.Value = Worksheets(ct).Name Then
BExists = True
Exit For
End If
Next ct
If BExists = False Then
For Each rngstartP In rngendP
If rngstartP.Value <> rngstartP.Offset(-1, 0).Value Then
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = rngstartP.Offset(1, 0)
End If
Next rngstartP
End If
End Sub
Any help is greatly appreciated
I can't Sort the file to group all hospital names together it has to stay in the order it is in....
I am attaching some sample data
Below is the code I am trying to use to Loop thru the data and create one worksheet for each Hospital Name:
Dim rngstartP As Range
Dim rngendP As Range
Dim BExists As Boolean
BExists = False
For ct = 1 To Worksheets.Count
If rngstartP.Value = Worksheets(ct).Name Then
BExists = True
Exit For
End If
Next ct
If BExists = False Then
For Each rngstartP In rngendP
If rngstartP.Value <> rngstartP.Offset(-1, 0).Value Then
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = rngstartP.Offset(1, 0)
End If
Next rngstartP
End If
End Sub
Any help is greatly appreciated