Enhance sub to paste charts as well

M

Max

Requesting help to enhance the sub below
There are some charts on the sheet (linked to the data on the same sheet)
that I would like pasted as well when the sub iterates through the DV in B3.
How could the sub be revised to do this? Thanks
----------------
Sub RunBatch()
'Tom Ogilvy
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("Branch")
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
 
P

Patrick Molloy

you could try to copy the sheet instead

this is an idea ...you shpould be able to adapt it easily enough
Dim sheetcount As Long
sheetcount = WorkSheets.Count
Sh.Copy After:=WorkSheets(sheetcount)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top