Single text file imported to multiple worksheets

[Guest]

I have a single text file with many different pieces of data that I'd like
divided into individual worksheets. The text file looks like this:

Server - test1 O/S Version
oracle5 HP-UX B.10.20
oracle7 HP-UX B.10.20
Server - test 2 O/S Version
whmz HP-UX B.10.20
Server - test 3 O/S Version
saturn HP-UX B.11.
oracle5 HP-UX B.10.20
pluto HP-UX B.10.20

As shown each new worksheet would start with the "Server - description"
field. I'd like to have that description also be the tab name if that's
possible.

 

[vezerid]

Hi,
the following VBA macro will distribute your data to new sheets. It
supposes that you have imported your master list in a single sheet
named "Data" and that data start from row 1 and occupy 3 columns. The
macro will create a new sheet as necessary when it encounters a break
line.
'-------------------------------------------------
Sub DistributeEntries()
Dim newSheet As Object
Dim srcSheet As Object

Set srcSheet = Sheets("Data") ' Change "Data" to sheet name

src_rn = 1 ' change to starting line of data
While srcSheet.Cells(src_rn, 1) <> ""
If Left(srcSheet.Cells(src_rn, 1), 6) = "Server" Then
Set newSheet = Sheets.Add
newSheet.Name = srcSheet.Cells(src_rn, 1)
newSheet.Move after:=Sheets(Sheets.Count)
dest_rn = 1
For i = 1 To 3
newSheet.Cells(dest_rn, i) = srcSheet.Cells(src_rn, i)
Next i
Else
dest_rn = dest_rn + 1
For i = 1 To 3
newSheet.Cells(dest_rn, i) = srcSheet.Cells(src_rn, i)
Next i
End If
src_rn = src_rn + 1
Wend
End Sub
'----------------------------------------------------

HTH
Kostis Vezerides

 

[Guest]

works great
THANKS!