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

Question

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

Report

josh07429

Posts: 16
Registration date: Friday June 23, 2017
Status: Member
Last seen: November 20, 2017

- Updated on Jul 5, 2017 at 11:28 PM
Jay - Apr 26, 2019 at 04:54 PM

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.

How to create new sheets for each row in excel?
Excel create sheets based on column - Best answers
Excel vba create new sheet with name from cell - How-To - Excel
Excel create unique id for each row ✓ - Forum - Excel
How to move a row in excel to another sheet automatically ✓ - Forum - Excel
Create duplicate rows in excel based on cell value ✓ - Forum - Excel
Macro to create new sheet and copy data - Forum - Excel

2 replies

Answer 2 / 2

vcoolio

Posts: 1278
Registration date: Thursday July 24, 2014
Status: Moderator
Last seen: February 16, 2021

220
Jul 7, 2017 at 03:23 AM

Hello Josh,

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

Cheerio,
vcoolio.

Ask a question

vcoolio · Accepted Answer · 2017-07-06T00:01:53-0400

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.

OMG! You're the best!!!

It works and its really fast, please make me your student :)

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

2 replies

Your reply

Subscribe To Our Newsletter!