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

Sub to automate process
 

Sorry, forgot to mention that the DV list in B3 is based on a defined range:
Batch

Thanks

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

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

Sub to automate process
 

Superb. Many thanks, Tom.

Max