Forum Discussion
Excel recorded macro to VBA to call from a different software
Hello,
I have no functional knowledge of VBA but needs to call a VBA from a different software (namely SAS) to create chart on multiple excel worksheets with specific formats.
I've recorded a macro for one sheet and used the following to repeat for all sheets included in the excel file but my x-axis label is off for the first two sheets and data labels for the last two sheets are not shown. I don't know how to reference the current sheet names properly.
Sub WorksheetLoop2()
' Declare Current as a worksheet object variable.
Dim Current As Worksheet
' Loop through all of the worksheets in the active workbook.
For Each Current In Worksheets
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Range("B5:M5")
''ActiveChart.SeriesCollection(1).XValues = _
'' "='Table 1 - Cross-tabular summary'!$B$1:$M$2"
ActiveChart.SeriesCollection(1).XValues = "=Current!B$1:$M$2"
ActiveChart.SeriesCollection(1).Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).ApplyDataLabels
ActiveChart.SeriesCollection(1).DataLabels.Select
ActiveChart.SeriesCollection(1).Select
'' (JS) change "Chart 1" to the numeric location of the chart rather than the name
'ActiveSheet.Shapes(I).IncrementLeft -434.25
'ActiveSheet.Shapes(I).IncrementTop -69.75
MsgBox Current.Name
Next
End Sub
11 Replies
Hello Junsh-
Please try the below code. See attached workbook for reference:
Note: I have named the charts in order... myCht1, myCht2, myCht3 .....
Sub WorksheetLoop2() ' Declare Current as a worksheet object variable. Dim Current As Worksheet Dim intLp As Integer ' Loop through all of the worksheets in the active workbook. For Each Current In Worksheets 'ActiveSheet.Shapes.Range("myCht" & intLp + 1).Select Current.ChartObjects("myCht" & intLp + 1).Activate With ActiveChart .ChartType = xlColumnClustered .SetSourceData Source:=Range("B5:M5") .SeriesCollection(1).XValues = "=Current!B$1:$M$2" .SeriesCollection(1).Select .PlotArea.Select .SeriesCollection(1).Select .SeriesCollection(1).ApplyDataLabels .SeriesCollection(1).DataLabels.Select .SeriesCollection(1).Select End With intLp = intLp + 1 'MsgBox Current.Name Next End Sub- Thank you Matt for your help. I almost gave up and though about recording a macro for all 13 sheets I n Unfortunately, the line of code, Current.ChartObjects("myCht" & intLp + 1).Activate had a run-time error, "The item with the specified name wasn't found". Would you please attach the file that worked for you? I guess there are numerous ways to go about looping a macro through all worksheets. At one point, my existing codes worked for referencing the correct data on each sheet, but when I tried my code again, it didn't behave quite as expected (i.e. generating three graphs on the first sheet with the wrong data). Any of your help is greatly appreciated! Junsh
Sorry I must have forgot to attach the workbook. See previous post. You have to rename the charts for it to work properly. Select the chart then change the name in the upper left hand corner. They need to be in the order the sheets are in...
