Split Column based on contents

[GeorgeAdhaero]

Hi

I did a query using VBScript to gain some server info and now have a text file in the following format

\\servera\
dog
cat
mong
\\serverb\
dog
cat
mong
ming
\\serverc\


What I am looking for is a clever macro to move the rows relating to each server to a new worksheet eg

tab1

\\servera\
dog
cat
mong

Tab2

\\serverb\
dog
cat
mong
ming

 

[Joe4]

Welcome to the Board!

This is a crude macro (it has far too many "activates" and "selects" to be considered sleek and elegant), but I was just trying to whip something up before I leave for the day and didn't have time to clean it up.

However, it should work just fine and do what you want. I even name each tab name with the server name (minus the slashes).

Sub MyServerMacro()
 
    Dim myStartSheet As String
    Dim myNewSheetName As String
    Dim myLastRow As Long
    Dim myValue As String
    Dim i As Long
 
    Application.ScreenUpdating = False
 
'   Capture name of data sheet
    myStartSheet = ActiveSheet.Name
 
'   Assuming all data is in column A, find last row of data
    myLastRow = Cells(Rows.Count, "A").End(xlUp).Row
 
'   Loop through all cells, populating values
    For i = 1 To myLastRow
        Sheets(myStartSheet).Activate
        myValue = Cells(i, "A")
        If Left(myValue, 2) = "\\" Then
            myNewSheetName = Replace(myValue, "\", "")
            Sheets.Add After:=Sheets(Sheets.Count)
            ActiveSheet.Name = myNewSheetName
            Range("A1") = myValue
        Else
            Sheets(myNewSheetName).Activate
            Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Activate
            ActiveCell = myValue
        End If
    Next i
 
    Application.ScreenUpdating = True
 
End Sub

 

[GeorgeAdhaero]

Thats great it worked a treat