Previous Topic Next Topic

Macro to create, and connect to, individual sheets.

Closed
lilyh75 - Updated on Jun 8, 2017 at 03:55 AM
vcoolio
Posts
1356
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 11, 2022
 - 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 reply

Answer 1 / 1
vcoolio
Posts
1356
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 11, 2022
 250
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]
        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.

https://www.dropbox.com/s/r5hwxx7tbdioutc/Lilyh75.xlsm?dl=0

I hope that this helps.

Cheerio,
vcoolio.
0