Solved: VBA to go the sheets specified and run the macro.

[kevvukeka]

Hi All,

I was able to develop a macro with this forums help. I need to combine 2,3 macros in to one. Kindly help.

Below is my requirement.

I have a workbook with 39 excel sheets, out of which 21 sheets have charts. In those charts I need to run a macro. The sheets in which I need to run the macro are stored in sheet2 first column with the help of macro.

The below macro extracts the sheet names to a new sheet in this workbook.
Sub sheetnames()
Set NewSheet = Sheets.Add(Type:=xlWorksheet)
For i = 1 To Sheets.Count
With NewSheet.Cells(i, 1)
.Value = CStr(Sheets(i).Name)
End With
Next i
End Sub

In this sheet I need to remove the sheet name which dont have charts. The remaining the cells which have the sheet name need to run the below macro in those sheets.

Sub colref()
Dim s As Series
Dim source As String, sh As String, sh1 As String
With ActiveChart
.SeriesCollection(1).formula =ActiveChart.SeriesCollection(2).formula
.SeriesCollection(2).formula =ActiveChart.SeriesCollection(3).formula
.SeriesCollection(3).formula =ActiveChart.SeriesCollection(4).formula
end with
Set s = ActiveChart.SeriesCollection(4)
source = s.Formula
sh = Replace(source, "AF", "AK")
ActiveChart.SeriesCollection(4).Formula = sh

End Sub

Can somebody help to combine these tow into 1. Where in I can have the option of selecting the sheetnames in which the above macro need to run.

Thanks a lot in advance.

[SamT]

This will put Chart Sheet names on NewSheet
Sub sheetnames()
Set NewSheet = Sheets.Add
For i = 1 To Charts.Count
NewSheet.Cells(i, 1).Text = Charts(i).Name)
Next i
End Sub

But You don't need the above If you do this. It runs the code with every Chart Sheet in the book.
Sub colref()
Dim Cht As Chart
For Each Cht In Charts
Dim s As Series
Dim source As String, sh As String, sh1 As String
With ActiveChart
'The rest of your code goes here
Next Cht
End Sub

[kevvukeka]

Hi Sam,

Thanks for the reply. But I dont want the macro to run in all sheets. there are 39 sheets out of which 21 sheets have charts(single charts), I have the code to update only these charts.

How can we run the macro on specific charts. By using the frist code, I can get a new sheet which will have all the sheet names in the first column. from that column I can remove the sheet names for which I dont want the to run the macro. But for the remaning sheets names, I want the code to be run.

Please help..

Thanks,
Praveen

[SamT]

Praveen,

I am sorry, I thought you wanted to run the code on all the chart type Sheets.

In VBA, "Chart" is a Sheet of type xlChart. "Charts" is the collection of those sheets.
VBA "ChartObject" is "chart" in English. VBA nomenclature for things Chart is hard to understand.

Sub colref()
Dim Cht As String
Dim Cel As Range
For Each Cel In Sheets("NewSheet").Range("A1:A21") 'Change newSHeet Name and Range "A1:A21" as needed
Cht = Cel.Text
With Charts(Cht) 'Charts(Cht) is a sheet.
'The rest of your code goes here
'...........
'...........
End With 'Charts(Cht)
Next Cel
End Sub

[kevvukeka]

Hi Sam,

After the new sheet is created I used the above code to run the macro on selected sheets. But it trhorws an error at "With Charts(Cht)" line. below is the code which modifed a little.

Sub colref()
Dim Cht As String
Dim s As Series
Dim source As String, sh As String, sh1 As String
Dim Cel As Range
For Each Cel In Sheets("Sheet1").Range("A1:A50") 'Change newSHeet Name and Range "A1:A21" as needed
Cht = Cel.Text
Do While Cel.Text <> vbNullString
With Charts(Cht)

.SeriesCollection(1).Formula = ActiveChart.SeriesCollection(2).Formula
.SeriesCollection(2).Formula = ActiveChart.SeriesCollection(3).Formula
.SeriesCollection(3).Formula = ActiveChart.SeriesCollection(4).Formula
Set s = ActiveChart.SeriesCollection(4)
source = s.Formula
sh = Replace(source, "AF", "AK")
ActiveChart.SeriesCollection(4).Formula = sh
End With
Loop
Next Cel


End Sub

Can you help where i missed it?

Thanks & Regards,
Praveen

[SamT]

Put "Cht = Cel.Text" line after "Do While" line

[mdmackillop]

Hi Praveen
Please use the green VBA button to format your code as shown

[kevvukeka]

Hi Sam,

I am not able to use do while loop. for the above code. If i give the range exactly the code is working fine.but if i want to use loop to skip balnk cells in the range, its falling into infinite loop.

[kevvukeka]

Hi MD,

Do i need to select the entire code and click the green VBA button to format it?

Regards,
Praveen

[mdmackillop]

That's correct.

[SamT]

Change

Do While Cel.Text <> vbNullString

to

Do While Cel.Text <> ""

[kevvukeka]

Hi Sam,

I updated the code as above.
The code is falling into infinite loop after working on first sheet.

Sub colref()
Dim Cht As String
Dim s As Series
Dim source As String, sh As String, sh1 As String
Dim Cel As Range
For Each Cel In Sheets("Sheet1").Range("A1:A50") 'Change newSHeet Name and Range "A1:A21" as needed

Do While Cel.Text <> ""
Cht = Cel.Text

Charts(Cht).Activate

With ActiveChart
.SeriesCollection(1).Formula = ActiveChart.SeriesCollection(2).Formula
.SeriesCollection(2).Formula = ActiveChart.SeriesCollection(3).Formula
.SeriesCollection(3).Formula = ActiveChart.SeriesCollection(4).Formula
Set s = ActiveChart.SeriesCollection(4)
source = s.Formula
sh = Replace(source, "AF", "AK")
ActiveChart.SeriesCollection(4).Formula = sh
End With
Loop

Next Cel


End Sub

[SamT]

Hi Sam,

I updated the code as above.
The code is falling into infinite loop after working on first sheet.

That is probably because you have an Event (_Change, _Activate, _etc,) sub somewhere.

Also, change the code like this
With Charts(Cht)
.SeriesCollection(1).Formula = ActiveChart.SeriesCollection(2).Formula
.SeriesCollection(2).Formula = ActiveChart.SeriesCollection(3).Formula
.SeriesCollection(3).Formula = ActiveChart.SeriesCollection(4).Formula
Set s = .SeriesCollection(4)
Source = s.Formula
.SeriesCollection(4).Formula = Replace(Source, "AF", "AK")
End With