View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
vezerid vezerid is offline
external usenet poster
 
Posts: 751
Default 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