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 9
K BAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
K BAuthor Commented:
The guy is simply a genius!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.