VBA to select Chart sheet name

[blackorchids2002]

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

 

[laborexcel]

It seems like this one does not work, any thoughts?
Thanks!

Hi Wigi,

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

 

[blackorchids2002]

Hi Wigi,

I have sorted it already. Thanks for the idea it really helps.

Here is the partial of the code that I expected to come out. I haven't use yet the other declarations as I intend to use it when saving the new copied chart sheets .


Sub Copy_Wsh()

Dim MyPath As String, FilesInPath As String, ShName As String, cName As String 'i'll use this later for saving the copied worksheet(s)
Dim mybook As Workbook
Dim sh As Worksheet
Dim oApp As Object
Dim oFolder As Variant
Dim ws As Chart
Dim LCounter As Long


'this will open the excel file from the selected folder
Set mybook = Workbooks.Open(UserForm.Select_FolderLabel.Caption)
mybook.UpdateLinks = xlUpdateLinksNever

'Change ScreenUpdating, Calculation and EnableEvents
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With

'selecting sheets
Set mybook = ActiveWorkbook
ReDim SheetsFound(0)
For Each ws In mybook.Charts
If (ws.Name) Like UserForm.CopyWNameTextBox.Value Then
LCounter = LCounter + 1
ws.Select False
SheetsFound(UBound(SheetsFound)) = ws.Name
ReDim Preserve SheetsFound(UBound(SheetsFound) + 1)
Else
Exit For
End If
Next ws
ReDim Preserve SheetsFound(UBound(SheetsFound) - 1)
Sheets(SheetsFound).Copy

Set ws = Nothing
Set mybook = Nothing

mybook.Close savechanges:=False
'Restore ScreenUpdating, Calculation and EnableEvents
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With

End Sub


Cheers,
Lhe

I still do not understand it, but maybe try:

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.

 

[laborexcel]

Thanks for sharing with your code although I do not really understand it. My question is a little different:

I am trying to arrange all charts in workbook: chart sheets and charts imbeded in regular sheets. Jon Peltier gave us VB code to arrange all charts in one regular sheet:http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html#arraycharts#ixzz1z1Nrq62U

So now my problem is how to copy all the charts(both in chart sheets and regular sheets) into a regular sheet?

Any help are appreciated.

Thanks!