Macro to create, and connect to, individual sheets.

lilyh75 - Updated on Jun 8, 2017 at 03:55 AM
vcoolio Posts 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 - Jun 7, 2017 at 04:46 AM
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?

1 response

vcoolio Posts 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
Updated on Jun 7, 2017 at 04:47 AM
Hello Lily,

I'm sure that we can help you out.

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]
Next key

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.

I hope that this helps.