Hello, I'm trying to create a macro that I can run that will create a new sheet based on a row. The new sheet will have all of the column headings A-Q on the new sheet and have the first columns information as the name of the new sheet and then will reference everything from the master sheet row on the individual sheet, but I also want the new sheet to reflect any changes if I make a change on the master worksheet. Is that doable in Excel?
Related:
Macro to create, and connect to, individual sheets.
Following is a VBA code that should do the task for you:-
Sub CreateNewShtsTransferData()
Dim sht As Worksheet
Dim lr As Long, x As Long
Dim ID As Object
Dim key As Variant
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
sht.Range("A1:A" & lr).AutoFilter 1, key
sht.[A1].CurrentRegion.Copy Sheets(key).[A1]
Sheets(key).Columns.AutoFit
sht.[A1].AutoFilter
Next key
sht.Select
Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "All done!", vbExclamation
End Sub
The code will create new sheets for each name in Column A without duplication and will transfer the relevant rows of data to the relevant individual sheet.
The individual sheets will refresh each time that you need to transfer updated data but will leave all data in your "Master" sheet (Sheet1) intact.
Following is the link to a little sample that I prepared for you so you can see how it would all work. Click on the "RUN" button to execute the code.
