Related:
- How to create new sheets for each row in excel?
- Excel create sheets based on column - Best answers
- Excel create new sheets based on data - Best answers
- Excel vba create new sheet with name from cell - How-To - Excel
- Excel create unique id for each row ✓ - Forum - Excel
- Macro to create new sheet and copy data ✓ - Forum - Excel
- Creating a macro that will create a new sheet and copy data from - Forum - Excel
- VBA - A macro to create multi-sheet pivot tables - How-To - Excel
2 replies
vcoolio
- Posts
- 1260
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- February 3, 2020
Hello Josh,
I don't see an image but see if the following code (untested) does the job for you:-
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.
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.
It works and its really fast, please make me your student :)