Related:
- Excel create new sheets based on data
- Excel create tabs from column - Best answers
- Excel - populate multiple sheets based on data entered in a main sheet - Best answers
- How To Create and Name Worksheets Based on a List via an Excel Macro - How-To - Excel
- Auto-populate info. from master sheet to multiple sheets ✓ - Forum - Excel
- Autopopulating multiple sheets from one main sheet ✓ - Forum - Excel
- Excel - A macro to create new workbook and copy data ✓ - Forum - Excel
- Excel - populate multiple sheets based on data entered in a main sheet ✓ - Forum - Excel
2 replies
Best answer
- Posts
- 1236
- Registration date
- Thursday July 24, 2014
- Status
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.
A few words of thanks would be greatly appreciated. Add comment
3408 users have said thank you to us this month
- Posts
- 16
- Registration date
- Friday June 23, 2017
- Last seen
- November 20, 2017
-It works and its really fast, please make me your student :)