Create new sheets based on Column A data. [Solved/Closed]
josh0742916
Posts Friday June 23, 2017Registration date
November 20, 2017
Last seen
-
Jul 5, 2017 at 08:38 PM - Latest reply: vcoolio1200
Posts Thursday July 24, 2014Registration dateModeratorStatus
October 12, 2018
Last seen
- Jul 7, 2017 at 03:23 AM
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.
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.
has helped 1286 users this month
It works and its really fast, please make me your student :)