Macro to create, and connect to, individual sheets.
Closed
lilyh75
-
Updated on Jun 8, 2017 at 03:55 AM
vcoolio
vcoolio
- Posts
- 1356
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- August 11, 2022
Related:
- Macro to create, and connect to, individual sheets.
- Macro to create new sheet and copy data - Forum - Excel
- Excel macro to create new sheet based on value in cells - Guide
- Macro to Create New Workbook and Copy Data at Each Change of X - Forum - Excel
- Excel - macro to create multiple sheets ✓ - Forum - Excel
- Excel - A macro to create new workbook and copy data ✓ - Forum - Excel
1 reply
vcoolio
Updated on Jun 7, 2017 at 04:47 AM
- Posts
- 1356
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- August 11, 2022
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:-
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.
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.