Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub to automate process
Looking for help with a sub to automate this process
Book is set to manual calc I have a DV list in B3 of say, 5 items (all items are text values), eg: Nov06 Dec06 Jan07 Feb07 Mar07 I select the 1st item in the DV: Nov06, press F9 to recalc (this takes around 1-2 mins). When recalc completes, I copy the entire sheet and do paste special as values & as formats into a new sheet inserted. I then rename the new sheet after the item selected, ie as: Nov06. I would then repeat the process for the next DV item: Dec06, and continue until all items in the DV list are exhausted. The book is then saved. Thanks for insights |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub to automate process
Sorry, forgot to mention that the DV list in B3 is based on a defined range:
Batch Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub to automate process
is the list typed directly in the data validation dialog or can it be picked
up somewhere else on the sheet. I will assume you typed a list into the dialog: Sub ABC() Dim sh As Worksheet, s As String Dim sh1 As Worksheet, r As Range Dim v As Variant, i As Long Set sh = ActiveSheet Set r = sh.Range("B3") s = r.Validation.Formula1 v = Split(s, ",") For i = LBound(v) To UBound(v) r.Value = v(i) Application.Calculate Worksheets.Add After:=Worksheets(Worksheets.Count) Set sh1 = Worksheets(Worksheets.Count) sh.Cells.Copy sh1.Cells.PasteSpecial xlValues sh1.Cells.PasteSpecial xlFormats sh1.Name = v(i) sh1.Range("B3").Validation.Delete Next End Sub with the values you show in your example, these get converted to dates when entered into the cell. If you want them to remain text strings, then change r.Value = v(i) to be r.Value = "'" & v(i) that is double quote, single quote, double quote -- Regards, Tom Ogilvy "Max" wrote: Looking for help with a sub to automate this process Book is set to manual calc I have a DV list in B3 of say, 5 items (all items are text values), eg: Nov06 Dec06 Jan07 Feb07 Mar07 I select the 1st item in the DV: Nov06, press F9 to recalc (this takes around 1-2 mins). When recalc completes, I copy the entire sheet and do paste special as values & as formats into a new sheet inserted. I then rename the new sheet after the item selected, ie as: Nov06. I would then repeat the process for the next DV item: Dec06, and continue until all items in the DV list are exhausted. The book is then saved. Thanks for insights |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub to automate process
for a range named Batch
Sub ABC() Dim sh As Worksheet, s As String Dim sh1 As Worksheet, r As Range Dim v As range, i As range Set sh = ActiveSheet Set r = sh.Range("B3") s = r.Validation.Formula1 set v = Range("Batch") For each i in v r.Value = "'" & i Application.Calculate Worksheets.Add After:=Worksheets(Worksheets.Count) Set sh1 = Worksheets(Worksheets.Count) sh.Cells.Copy sh1.Cells.PasteSpecial xlValues sh1.Cells.PasteSpecial xlFormats sh1.Name = i.Text sh1.Range("B3").Validation.Delete Next End Sub -- Regards, Tom Ogilvy "Tom Ogilvy" wrote: is the list typed directly in the data validation dialog or can it be picked up somewhere else on the sheet. I will assume you typed a list into the dialog: Sub ABC() Dim sh As Worksheet, s As String Dim sh1 As Worksheet, r As Range Dim v As Variant, i As Long Set sh = ActiveSheet Set r = sh.Range("B3") s = r.Validation.Formula1 v = Split(s, ",") For i = LBound(v) To UBound(v) r.Value = v(i) Application.Calculate Worksheets.Add After:=Worksheets(Worksheets.Count) Set sh1 = Worksheets(Worksheets.Count) sh.Cells.Copy sh1.Cells.PasteSpecial xlValues sh1.Cells.PasteSpecial xlFormats sh1.Name = v(i) sh1.Range("B3").Validation.Delete Next End Sub with the values you show in your example, these get converted to dates when entered into the cell. If you want them to remain text strings, then change r.Value = v(i) to be r.Value = "'" & v(i) that is double quote, single quote, double quote -- Regards, Tom Ogilvy "Max" wrote: Looking for help with a sub to automate this process Book is set to manual calc I have a DV list in B3 of say, 5 items (all items are text values), eg: Nov06 Dec06 Jan07 Feb07 Mar07 I select the 1st item in the DV: Nov06, press F9 to recalc (this takes around 1-2 mins). When recalc completes, I copy the entire sheet and do paste special as values & as formats into a new sheet inserted. I then rename the new sheet after the item selected, ie as: Nov06. I would then repeat the process for the next DV item: Dec06, and continue until all items in the DV list are exhausted. The book is then saved. Thanks for insights |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub to automate process
Superb. Many thanks, Tom.
Max |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
way to automate this process | Excel Discussion (Misc queries) | |||
Macro to automate process | Excel Discussion (Misc queries) | |||
need to automate process | Excel Discussion (Misc queries) | |||
Automate deletion process | Excel Worksheet Functions | |||
Automate an Excel process | Excel Programming |