Report

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

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.
See more 
Helpful
+1
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]
        ws.Columns.AutoFit
            sht.[A1].AutoFilter
Next key

sht.Select
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.

Cheerio,
vcoolio.
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 :)
Reply
Leave a comment
Helpful
+0
plus moins
Hello Josh,

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

Cheerio,
vcoolio.
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!