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
|