trY;

Sub SetUp()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim irow As Long, orow As Long
Dim Lastrow As Long

Dim wsname As String

Set ws1 = Worksheets("List")
Set ws2 = Worksheets("Template")


With ws1

Lastrow = .Cells(Rows.Count, 1).End(xlUp).Row

For irow = 2 To Lastrow
wsname = .Cells(irow, 2)
Sheets("Template").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = wsname
ActiveSheet.Range("G1") = wsname
.Cells(irow, 3).Formula = "='" & wsname & "'!K70"
Next irow

End With


End Sub

"driller" wrote:

Thanks in advance,

Here it goes.

In a current workbook, i have only two existing sheets named as...
a) LIST
b) TEMPLATE (in here, search formulas are linked to other workbooks)
---------------
SHEETNAME:"LIST"
***looks like this start from A1***
SERIES REC_ID
1 SC-0001
2 SC-0202
3 SC-0350
4 SC-0125
5 T-01
6 TEMP-01
7 T-02
8 TEMP-02
9 SC-0205
10 SC-0223
11 SC-0225
....
REC_ID has a maximum of 10 characters (letters,"-",numbers).
SERIES can reach 100 minimum, but it may be better if at least 80% of excel
limitation has been reached as the maximum number of sheets. If the
max(SERIES) exceeds your program, a pop-up may be suitable for alarm.
-----------

I will run your macro (or whatever) once in order.....

a) to auto-populate sheets *copy-cat* from sheet Template. The total
populated sheets equal to Max(Series).
b) Then when the sheets are populated, each sheet must be auto-named with
each corresponding REC_ID.
c) Then for each populated sheet, the value of cell G1 must be replaced
with the corresponding sheet name from REC_ID. (e.g. G1="SC-0001").
d) lastly, take values from cell K70 (populated sheets) and paste them as
linked values col C in sheet "LIST". It will look like this...
SERIES EMP_ID col C
1 SC-0001 25,600.00 (=SC-0001!K70)
2 SC-0202 26,106.00
3 SC-0350 26,706.00
4 SC-0125 27,212.00
5 T-01 27,812.00
6 TEMP-01 28,318.00
7 T-02 28,918.00
8 TEMP-02 29,424.00
9 SC-0205 30,024.00
10 SC-0223 30,530.00
11 SC-0225 31,130.00 (=SC-0225!K70)
....
The "TEMPLATE" sheet are already formatted for printing...
I will be glad to share the file once this is completed thru your
support...I have a target until tomorrow afternoon to print the
MACRO-populated sheets and the LIST sheet.

good luck with best regards,
driller
--
*****
birds of the same feather flock together..

--
*****
birds of the same feather flock together..