Maybe a macro or whatever can do this easily...

[driller]

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

 

[Toppers]

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

 

[Dave Peterson]

Check your other post, too.

And you wasted my time or Toppers time.

 

[driller]

Toppers,

how can i thank you with deep regards ?

<hehehe> u got it ! Looking forward for your gold!!!

i make small change in ur SETUP() and test run it for 3 rows of data on
"LIST"....
it smoothly run and gave me 3 new worksheet in less than a minute <whew!>
kinda fast!!!
--------------------
Sub SetUp()

Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 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")
Set ws3 = Worksheets("CAT")
Set ws4 = Worksheets("DATA")

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, 30).Formula = "='" & wsname & "'!g54"
Next irow

End With


End Sub

---------------
a) how shall i repeat the copy of *other* cell(s) value from each new
worksheet and paste them as column in "LIST" worksheet ,the same way u did
with <"='" & wsname & "'!g54">...?
b) i forgot that the new sheets must contain values only *no formulas*
before i make a group printing...
may you please include them in the above code...

the group printing will happen as soon as i avoid error results in the
*template*...

||||------>
driller

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

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]

Toppers,
I guess your busy...i will re-post my other questions to have the SETUP()
macro produce a little more summarized data in the "LIST"including hyperlinks
to the new worksheets ...thanks again...i've tested with printfile the 62 new
printable worksheets<one page/per sht> leaving me with a new file of 11MB
only...<8.3MB is the original file>...
after the producing the 62 new sheets, I have discovered a lot about
dyna-formulas.

thanks also to Mr. Dave Peterson...

regards,
driller
--
*****
birds of the same feather flock together..

Toppers,

how can i thank you with deep regards ?

<hehehe> u got it ! Looking forward for your gold!!!

i make small change in ur SETUP() and test run it for 3 rows of data on
"LIST"....
it smoothly run and gave me 3 new worksheet in less than a minute <whew!>
kinda fast!!!
--------------------
Sub SetUp()

Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 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")
Set ws3 = Worksheets("CAT")
Set ws4 = Worksheets("DATA")

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, 30).Formula = "='" & wsname & "'!g54"
Next irow

End With


End Sub

---------------
a) how shall i repeat the copy of *other* cell(s) value from each new
worksheet and paste them as column in "LIST" worksheet ,the same way u did
with <"='" & wsname & "'!g54">...?
b) i forgot that the new sheets must contain values only *no formulas*
before i make a group printing...
may you please include them in the above code...

the group printing will happen as soon as i avoid error results in the
*template*...

||||------>
driller