copy data from list to specific cells after macro creates sheets based on template

Leeward904

New Member
Joined
May 5, 2021
Messages
12
Office Version
  1. 2019
Platform
  1. Windows
I apologize if this has been posted in another discussion, I can't seem to find it...

I have the VBA macro to create new templated worksheets and name each sheet based on a master list. What I can't quite figure out is how do I include the next step to copy and paste the data from the same master list into the specific cells on each new templated worksheet.

Example:
Step 1- VBA creates a new worksheet based on the template for each specification number in Column A of the attached picture
Step 2- Each new worksheet is titled the number of each specification in Column A
Step 3- I need the macro to also copy the specification number for the corresponding worksheet into cell B1, the Spec Title into the corresponding worksheet into cell B2, and finally copy the corresponding word "Base/ Option" into the corresponding worksheet into cell B3.

I really appreciate any support anyone can provide as I am stuck!
 

Attachments

  • masterlist of items.PNG
    masterlist of items.PNG
    48.4 KB · Views: 50

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Put this code into a code module in the workbook containing the list of Specification Numbers.

I'm not sure how you want to run this macro but place your cursor anywhere in the subCreateWorksheets procedure and press F5
and it will produce the worksheets that you need.

Any enhancements needed, just let me know.

VBA Code:
Option Explicit

Public Sub subCreateWorksheets()
Dim Ws As Worksheet
Dim rng As Range

    Set Ws = Worksheets("Master")
    
    For Each rng In Ws.Range("A1").CurrentRegion.Columns(1).Cells
        If rng.Row <> 1 Then
            If Not fncSheet_Exists(rng.Value) Then
                Worksheets.Add(After:=Sheets(Sheets.Count)).Name = rng.Value
                ActiveSheet.Range("B1:B3").Value = WorksheetFunction.Transpose(rng.Resize(1, 3))
            End If
        End If
    Next rng
    
    Ws.Activate

End Sub

Private Function fncSheet_Exists(WorkSheet_Name As String) As Boolean
Dim Work_sheet As Worksheet
 
    fncSheet_Exists = False
 
    For Each Work_sheet In ThisWorkbook.Worksheets
     
        If Work_sheet.Name = WorkSheet_Name Then
            fncSheet_Exists = True
        End If
     
    Next
 
End Function
 
Upvote 0
Put this code into a code module in the workbook containing the list of Specification Numbers.

I'm not sure how you want to run this macro but place your cursor anywhere in the subCreateWorksheets procedure and press F5
and it will produce the worksheets that you need.

Any enhancements needed, just let me know.

VBA Code:
Option Explicit

Public Sub subCreateWorksheets()
Dim Ws As Worksheet
Dim rng As Range

    Set Ws = Worksheets("Master")
  
    For Each rng In Ws.Range("A1").CurrentRegion.Columns(1).Cells
        If rng.Row <> 1 Then
            If Not fncSheet_Exists(rng.Value) Then
                Worksheets.Add(After:=Sheets(Sheets.Count)).Name = rng.Value
                ActiveSheet.Range("B1:B3").Value = WorksheetFunction.Transpose(rng.Resize(1, 3))
            End If
        End If
    Next rng
  
    Ws.Activate

End Sub

Private Function fncSheet_Exists(WorkSheet_Name As String) As Boolean
Dim Work_sheet As Worksheet
 
    fncSheet_Exists = False
 
    For Each Work_sheet In ThisWorkbook.Worksheets
   
        If Work_sheet.Name = WorkSheet_Name Then
            fncSheet_Exists = True
        End If
   
    Next
 
End Function
HighAndWilder, thank you for the assistance and I apologize for my delayed response (i have a sick 1 year at the house and that is quite time consuming!).

This code is exactly what i need! the only thing i have left is to put the string of code to have it create the new worksheets as a copy of the template from tab titled "estimate template".

Aside from that this code creates the worksheets and titles each tab as the specification number correctly and applies the correct data into cells B1 - B3.

I really appreciate your help with this.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top