Enhance sub to paste charts as well
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 |
Enhance sub to paste charts as well
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) "Max" wrote: 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 |
Enhance sub to paste charts as well
Thanks, Patrick.
|
All times are GMT +1. The time now is 11:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com