Single text file imported to multiple worksheets
 

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.

Single text file imported to multiple worksheets
 

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

Single text file imported to multiple worksheets
 

works great
THANKS!

"vezerid" wrote:

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