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

[arszzyoung94]

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!

 

[Herakles]

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?

 

[arszzyoung94]

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!

 

[Herakles]

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?

 

[arszzyoung94]

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

 

[Wookiee]

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.

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