Can a macro - auto populate sheets...

[Guest]

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 once (best) 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

 

[Dave Peterson]

The number of worksheets in your workbook is limited by your pc--you can look
for specifications in excel's help. I have no idea if you can have 1000
worksheets added.

But this will work until it breaks:

Option Explicit
Sub testme()
Dim ListWks As Worksheet
Dim TemplWks As Worksheet
Dim NewWks As Worksheet
Dim ListRng As Range
Dim myCell As Range

Set ListWks = Worksheets("list")
Set TemplWks = Worksheets("template")

With ListWks
Set ListRng = .Range("b2", .Cells(.Rows.Count, "B").End(xlUp))
End With

For Each myCell In ListRng.Cells
With TemplWks
.Copy _
after:=.Parent.Worksheets(.Parent.Worksheets.Count)
End With
Set NewWks = ActiveSheet

On Error Resume Next
NewWks.Name = myCell.Value
If Err.Number <> 0 Then
MsgBox "Rename: " & NewWks.Name & " manually!"
Err.Clear
End If

With NewWks.Range("G1")
.NumberFormat = "@" 'make it text
.Value = myCell.Value
End With

myCell.Offset(0, 1).Formula = "='" & NewWks.Name & "'!k70"
Next myCell

End Sub

==========
Personally, making 1000 worksheets doesn't make a lot of sense to me.

If you're looking to print the template with each partnumber, then you could
just populate the template worksheet, copy|paste special values to the list (to
get that value from K70), print the template worksheet and do it again and again
and ...

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Guest]

Hi Sir Dave,

first of all
thanks a lot for the tester macro...testme()

I am double checking the formulas with many named ranges in the TEMPLATE
tab...i will insert the code after few hours and i will be back with this
post for confirmation of our effort.

regards,
driller

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

The number of worksheets in your workbook is limited by your pc--you can look
for specifications in excel's help. I have no idea if you can have 1000
worksheets added.

But this will work until it breaks:

Option Explicit
Sub testme()
Dim ListWks As Worksheet
Dim TemplWks As Worksheet
Dim NewWks As Worksheet
Dim ListRng As Range
Dim myCell As Range

Set ListWks = Worksheets("list")
Set TemplWks = Worksheets("template")

With ListWks
Set ListRng = .Range("b2", .Cells(.Rows.Count, "B").End(xlUp))
End With

For Each myCell In ListRng.Cells
With TemplWks
.Copy _
after:=.Parent.Worksheets(.Parent.Worksheets.Count)
End With
Set NewWks = ActiveSheet

On Error Resume Next
NewWks.Name = myCell.Value
If Err.Number <> 0 Then
MsgBox "Rename: " & NewWks.Name & " manually!"
Err.Clear
End If

With NewWks.Range("G1")
.NumberFormat = "@" 'make it text
.Value = myCell.Value
End With

myCell.Offset(0, 1).Formula = "='" & NewWks.Name & "'!k70"
Next myCell

End Sub

==========
Personally, making 1000 worksheets doesn't make a lot of sense to me.

If you're looking to print the template with each partnumber, then you could
just populate the template worksheet, copy|paste special values to the list (to
get that value from K70), print the template worksheet and do it again and again
and ...

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Dave Peterson]

Don't forget to check your other post, too.

Hi Sir Dave,

first of all
thanks a lot for the tester macro...testme()

I am double checking the formulas with many named ranges in the TEMPLATE
tab...i will insert the code after few hours and i will be back with this
post for confirmation of our effort.

regards,
driller

 

[Guest]

thank you!

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

Don't forget to check your other post, too.