Copy template sheet multiple times, renamed assigned value to specific cells based on a list in another worksheet

arszzyoung94

New Member
Joined
Feb 8, 2023
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Copy template sheet multiple times, renamed assigned value to specific cells based on a list in another worksheet

Hi, All. I am new to excel VBA

I am trying to find a macro that functions as stated below:

I have a template worksheet (named 'template') and a data worksheet (named 'data')

The 'template' worksheet is a report template

The 'data' worksheet has 4 columns of data:

A1 column is a name list (drawing name)

B1 column is a name list (part name)

C1 column is an value list (Qty)

D1 column is an value list (item number)

Firstly I would like to copy the 'template' worksheet multiple times and rename each copied worksheet based on the A1 list in 'data' worksheet

Secondly, copy names from A1 list in 'data' worksheet to cell D7 in each corresponding renamed template worksheet.

Thirdly, copy names from B1 list in 'data' worksheet to cell I7 in each new worksheet correspondingly.

Fourthly, copy value from C1 list in Data sheet to cell D8 in each new worksheet

Lastly, copy value from D1 column list in Data sheet to cell J3 in each new worksheet

Thank you very much!
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
How are the values in D7, I7, D8 and J3 to be formatted?

Do you want line breaks between the list items or just a delimiter such as a comma?
 
Upvote 0
How are the values in D7, I7, D8 and J3 to be formatted?

Do you want line breaks between the list items or just a delimiter such as a comma?
Hi, Herakles,

There is no specific formation for the value so I think general formation is fine.
Sorry I dont't quite understand your next question as I am not familiar with excel and I am currently using Excel in my language version (not English).
Does 'line breaks between the list items' mean that it is easier to understand the arrangement of the code and using delimiter can shorten the code?
Sorry for the guessing.
But I think anything that can achieve the goals works for me.

Thank you for your reply!
 
Upvote 0
You are asking for data from multiple lines to be written into a single cell.

Do you want the individual data items to appear to be on a seperate line within this single cell?

Do you want the entire list from columns A-D to be copied into the single cells irrespective of the drawing name?

Can you post a screen shot of how you want these sheets to look?
 
Upvote 0
You are asking for data from multiple lines to be written into a single cell.

Do you want the individual data items to appear to be on a seperate line within this single cell?

Do you want the entire list from columns A-D to be copied into the single cells irrespective of the drawing name?

Can you post a screen shot of how you want these sheets to look?
Hi,

I have uploaded some screen-shot of the wanted worksheet.
Basically the the template report (the 1st screen shot) is copied multiple times and renamed based on the A1 name list (as shown in 2nd screen shot),the 3rd and 4th screen-shot show the newly created worksheets and the data is loacted to the desired cell, respectively. So this is how I want the code to do.

Thank you
 

Attachments

  • 1. templat worksheet.PNG
    1. templat worksheet.PNG
    33.1 KB · Views: 36
  • 2. data worksheet.PNG
    2. data worksheet.PNG
    24.9 KB · Views: 35
  • 3. renamed worksheet 1.png
    3. renamed worksheet 1.png
    29.1 KB · Views: 35
  • 4. renamed worksheet 2.png
    4. renamed worksheet 2.png
    28 KB · Views: 34
Upvote 0
Hello. I take it that for each item in Column A on your Data sheet, you want to copy the template and populate the values. I believe this macro should do that for you.

VBA Code:
Sub ArsZZ()
'Crafted by Wookiee at MrExcel.com


Dim lngLoop          As Long
Dim lngQty           As Long
Dim strDrawing       As String
Dim strItemNmbr      As String
Dim strSheet         As String
Dim strPart          As String
Dim arrSheets        As Variant
Dim wksData          As Worksheet
Dim wksTemplate      As Worksheet


With ThisWorkbook

  Set wksData = .Sheets("Data")
  Set wksTemplate = .Sheets("Template")

End With

With wksData

  For lngLoop = 1 To .Range("A" & Rows.Count).End(xlUp).Row
  
  strDrawing = .Range("A" & lngLoop)
  strPart = .Range("B" & lngLoop)
  lngQty = .Range("C" & lngLoop)
  strItemNmbr = .Range("D" & lngLoop)

  wksTemplate.Copy After:=.Parent.Sheets(Sheets.Count)

  With ActiveSheet

    .Name = strDrawing
    .Range("D7") = strDrawing
    .Range("I7") = strPart
    .Range("D8") = lngQty
    .Range("J3") = strItemNmbr

  End With

  Next lngLoop

End With

End Sub
 
Upvote 0

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

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