Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can a macro - auto populate sheets...
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 -- ***** birds of the same feather flock together.. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can a macro - auto populate sheets...
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 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 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 -- ***** birds of the same feather flock together.. -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can a macro - auto populate sheets...
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.. "Dave Peterson" wrote: 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 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 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 -- ***** birds of the same feather flock together.. -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can a macro - auto populate sheets...
Don't forget to check your other post, too.
driller wrote: 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.. "Dave Peterson" wrote: 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 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 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 -- ***** birds of the same feather flock together.. -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can a macro - auto populate sheets...
thank you!
regards, driller -- ***** birds of the same feather flock together.. "Dave Peterson" wrote: Don't forget to check your other post, too. driller wrote: 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.. "Dave Peterson" wrote: 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 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 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 -- ***** birds of the same feather flock together.. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to auto-populate a phone # | Excel Discussion (Misc queries) | |||
How to populate sheets from a specified set of cells.. | Excel Worksheet Functions | |||
Auto populate with value | Excel Discussion (Misc queries) | |||
auto populate | Excel Discussion (Misc queries) | |||
Auto Populate a cell | Excel Discussion (Misc queries) |