VBA to select Chart sheet name

blackorchids2002

Board Regular
Joined
Dec 29, 2011
Messages
135
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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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
 

blackorchids2002

Board Regular
Joined
Dec 29, 2011
Messages
135
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
 

blackorchids2002

Board Regular
Joined
Dec 29, 2011
Messages
135
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
 

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

Hmmm, I read above:

I want to create a macro to copy the chart sheet name into a new workbook.

So, will they be copied in their own separate file?
 

blackorchids2002

Board Regular
Joined
Dec 29, 2011
Messages
135

ADVERTISEMENT

Hi Wigi,

I want the whole sheet including the chart on it to be selected and copied into a new file.

I hope this is clear... thank you again..


Hi Wigi,

I just need it in one file only...Thanks for the help in advance..
 

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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...
 

blackorchids2002

Board Regular
Joined
Dec 29, 2011
Messages
135
Hi Wigi,

It is not redundant. There will be grouping of sheets and will be saved in a file according to their groupings.
 

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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.
 

Forum statistics

Threads
1,144,164
Messages
5,722,855
Members
422,461
Latest member
kelleys315

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
Top