VBA to select Chart sheet name

blackorchids2002

Board Regular
Joined
Dec 29, 2011
Messages
138
Hi Masters,

Please help me...

I have a workbook with sheet(s) that contains only a Chart. The sheet name is the same as the chart title.
I want to create a macro to copy the chart sheet name into a new workbook.

If I simply do the recording of macro to copy the chart sheet name and re-run it I encountered an error. I guess it's on the sheet to chart object collection or property.

Appreciate if you can help me with the code.


Thanks,
Lhe
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Hello Lhe

This routine can be used:

Code:
Sub test()    
    Dim wbTarget As Workbook
    Dim wbSource As Workbook
    Dim ws As Chart
    Dim lCounter As Long
    
    Set wbSource = ActiveWorkbook
    Set wbTarget = Workbooks.Add
    
    For Each ws In wbSource.Charts
        lCounter = lCounter + 1
        wbTarget.Worksheets(1).Cells(lCounter, 1).Value = ws.Name
    Next
    
    Set wbSource = Nothing
    Set wbTarget = Nothing
    Set ws = Nothing


End Sub
 
Upvote 0
Hi Wigi,

Appreciate your reply.

Actually I used a userform with a textbox to input the sheet name using a wildcard character let say Income*.

I wanted to copy the Chart SheetName based on the textbox value "Income*". In the workbook it contains 2 or more Chart Sheet Name beginning "Income*".


Please help how to code that.


many thanks,
Lhe
 
Upvote 0
Hi Wigi,

What I wanted to copy is the Chart Sheet...


Thanks,
Lhe


Hello Lhe

This routine can be used:

Code:
Sub test()    
    Dim wbTarget As Workbook
    Dim wbSource As Workbook
    Dim ws As Chart
    Dim lCounter As Long
    
    Set wbSource = ActiveWorkbook
    Set wbTarget = Workbooks.Add
    
    For Each ws In wbSource.Charts
        lCounter = lCounter + 1
        wbTarget.Worksheets(1).Cells(lCounter, 1).Value = ws.Name
    Next
    
    Set wbSource = Nothing
    Set wbTarget = Nothing
    Set ws = Nothing


End Sub
 
Upvote 0
Why don't you copy the entire file?
You only have sheets with charts in your original file.
And you want to copy them a 1 new file.
Seems redundant if you ask me...
 
Upvote 0
Hi Wigi,

It is not redundant. There will be grouping of sheets and will be saved in a file according to their groupings.
 
Upvote 0
I still do not understand it, but maybe try:

Code:
Sub test()
    Dim ws As Chart
    
    For Each ws In ActiveWorkbook.Charts
         ws.Copy
    Next

    Set ws = Nothing

End Sub

It will copy the Chart sheets to new workbooks.

But I confirm that your explanations remain vague.
 
Upvote 0

Forum statistics

Threads
1,218,845
Messages
6,144,807
Members
450,567
Latest member
Mplz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top