Create worksheets named from a column and copy data from remaining columns to newly created worksheets

A                         B                          C                        D                          E
1  Server1         Windows 2003          2 x XEON     Adobe Acrobat       Version 8.0
2                                                                               CommVault            Version 9.0
3                                                                                     Java                   Version 6.0
4 Server 2         Windows 2008          1 x XEON     Adobe Acrobat       Version 6.0
5                                                                               MS Silverlight        Version 4.0
6                                                                                     McAfee             Version 4.6


I would like to start with a Workbook with just the above Worksheet.
The VBA would create tab(s) (aka worksheet(s)) from Column A  (example., A1 then A4)..
Next it would copy all row data from column B, C, D & E until it hit the next value in column A (in this case A4, or Server 2).  It would then populate the tab named Server 2 with the row data from column B, C, D & E
LVL 8
K BAsked:
Who is Participating?
 
Saurabh Singh TeotiaCommented:
Use this code for the same...

Sub movedata()
    Dim i As Long, lrow As Long
    Dim sr As Long, er As Long
    Dim ws As Worksheet, ws1 As Worksheet

    Set ws = ActiveSheet

    lrow = ws.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    i = 1

    Do Until i > lrow

        If ws.Cells(i, "A").Value <> "" Then
            Sheets.Add
            ActiveSheet.Name = ws.Cells(i, "A").Value
            Set ws1 = ActiveSheet
            ws.Select
            sr = i

            i = i + 1
        Else
            i = i + 1
        End If


        If ws.Cells(i, "A").Value <> "" Or i = lrow Then
            If i = lrow Then
                er = i
            Else
                er = i - 1
            End If

            ws.Range("A" & sr & ":e" & er).Copy ws1.Range("A1")
        End If

    Loop

End Sub

Open in new window

0
 
Saurabh Singh TeotiaCommented:
You can try the following code to do what you are looking for...

Sub movedata()
    Dim i As Long, lrow As Long
    Dim sr As Long, er As Long
    Dim ws As Worksheet, ws1 As Worksheet

    Set ws = ActiveSheet

    lrow = ws.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    i = 1

    Do Until i > lrow

        If ws.Cells(i, "A").Value <> "" Then
            Sheets.Add
            ActiveSheet.Name = ws.Cells(i, "A").Value
            Set ws1 = ActiveSheet
            ws.Select
            sr = i

            i = i + 1
        Else
            i = i + 1
        End If


        If ws.Cells(i, "A").Value <> "" Or i = lrow Then
            If i = lrow Then
                er = i
            Else
                er = i - 1
            End If

            ws.Range("B" & sr & ":e" & er).Copy ws1.Range("A1")
        End If


    Loop



End Sub

Open in new window


Saurabh...
0
 
K BAuthor Commented:
Saurabh,

First of all this is amazing!!! You are truly a genius!!
Many many thanks.
I have one more question, if I may.  Can this also include the first column in newly created tabs?
I would like to be able to do a search for the server name and be able to find it.  Otherwise, I will have to scroll for the tab.

Again, this is so very helpful and thank you so much!

K.B.
0
 
K BAuthor Commented:
The guy is simply a genius!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.