VBA Refer to worksheet vs chart sheet

vba charts worksheet refer
Jesse Smothermon picture Jesse Smothermon · Jul 23, 2011 · Viewed 19.7k times · Source

I'm trying to write a small function that takes in a filepath (where the workbook was saved at), targetpath (where the pdf will be saved to), and a string of tab names (pipe (|) delimited) in excel.

The user of the function doesn't have to input a string of tab names (it's optional) and if they don't, I want to select all of the visible tabs and print them. This would be in the case if the user has 50 charts in separate worksheets and don't want to write a string like "Chart1|Chart2|...."

Code:

For Each WSO.Name In WBO.Worksheets 
    strSheets = strSheets & WSO.Name & "|" 
Next WSO

strSheets = Left(strSheets, Len(strSheets) - 1) 
arraySheets() = Split(strSheets, "|")

WBO.Sheets(arraySheets()).Select     
WBO.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _ 
    strFilePath, Quality:=xlQualityStandard, _ 
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _ 
    True

There's two problems with the For Each loop: it doesn't grab any sheets such as "Chart1", it only grabs sheets such as "Sheet1". Also, it will grab hidden sheets so that when I try to select them all I get an out of bounds error.

I didn't know if a Chart sheet is referred to differently then a regular sheet or why hidden sheets are also chosen.

Answer

GSerg picture GSerg · Jul 23, 2011

Use WBO.Sheets instead of WBO.Worksheets in the loop.

Verify that WSO.Visible = xlSheetVisible to filter out hidden sheets.

Related questions

How can I color dots in a xy scatterplot according to column value?

Consider the following worksheet: A B C D 1 COMPANY XVALUE YVALUE GROUP 2 Apple 45 35 red 3 Xerox 45 38 red 4 KMart 63 50 orange 5 Exxon 53 59 green I have used the scatterplot function in Excel to create the following chart: However, each point in the chart has …

Read More
Excel VBA Script to dynamically add series to chart

I'm trying to dynamically add multiple series to a line chart. I don't know beforehand how many series there are so it needs to be dynamic. What I've come up with but doesn't work is the following: The sheet ActiveSheet (…

Read More
Absolute Position of Chart Using VBA

I can use VBA to create a clustured column charty using the following code: ActiveSheet.Shapes.AddChart.Select ActiveChart.ChartType = xlColumnClustered However this is normally positioned in the centre of my screen. I can have it moved using code such …

Read More