Menu
Ask a question Report

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

Posts
16
Registration date
Friday June 23, 2017
Status
Member
Last seen
November 20, 2017
 -
Excel Ask a question Report
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 

2 replies

Best answer
Report
Posts
1251
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 29, 2019
 207
1
Thank you
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.

Say "Thank you" 1

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 5641 users have said thank you to us this month

Report
josh07429
Posts
16
Registration date
Friday June 23, 2017
Status
Member
Last seen
November 20, 2017
 -
OMG! You're the best!!!

It works and its really fast, please make me your student :)
Report Respond to Jay
-
Just tried this and it works great! Thanks!
Report
Posts
1251
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 29, 2019
 207
1
Thank you
Hello Josh,

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

Cheerio,
vcoolio.

Say "Thank you" 1

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 5641 users have said thank you to us this month

Ask a question