Create new sheets based on Column A data. [Solved]

Ask a question josh07429 15Posts Friday June 23, 2017Registration date July 7, 2017 Last seen - Last answered on Jul 7, 2017 at 03:23 AM by vcoolio

Is there a way to create a new tab based on column A?
For example: I want to create a new tab for each vehicle, copy and paste all of vehicle 722's columnA to Column F cells then move on to create a new tab for 725 and copy and paste all 725's Column A to F to the new tab. if the macro can also name the tab name as vehicle number that would be great.

this photo is just an example, Im dealing with 4000 rows so I am wondering if this is possible with a macro.

Thanks in advance.
plus moins
Hello Josh,

I don't see an image but see if the following code (untested) does the job for you:-

Sub CreateNewShtsTransferData()

    Dim lr As Long, x As Long
    Dim ID As Object
    Dim key As Variant
    Dim sht As Worksheet
    Dim ws As Worksheet
Set sht = Sheet1
Set ID = CreateObject("Scripting.Dictionary")
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
lr = sht.Range("A" & Rows.Count).End(xlUp).Row
For x = 2 To lr
        If Not ID.Exists(sht.Range("A" & x).Value) Then
            ID.Add sht.Range("A" & x).Value, 1
       End If
Next x

For Each key In ID.keys
        If Not Evaluate("ISREF('" & key & "'!A1)") Then
        Worksheets.Add(After:=Sheets(Sheets.Count)).Name = key
        End If

Set ws = Worksheets(CStr(key)) '---->This converts the vehicle numbers to text values otherwise the code will error.
sht.Range("A1:A" & lr).AutoFilter 1, key
    sht.[A1].CurrentRegion.Copy ws.[A1]
Next key

Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "All done!", vbExclamation

End Sub

As you have a large data set, the code uses the Scripting Dictionary (a very quick method) to create sheets from the values in Column A and then transfer the relevant rows of data to each individual sheet.
Each sheet is named after each value in Column A without duplication.

Place the code in a standard module and assign it to a button.

I hope that this helps.

Was this answer helpful?  
josh07429 15Posts Friday June 23, 2017Registration date July 7, 2017 Last seen - Jul 6, 2017 at 11:18 AM
OMG! You're the best!!!

It works and its really fast, please make me your student :)
Leave a comment
plus moins
Hello Josh,

You're welcome. I'm glad that it works for you and that I was able to assist.

Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!