Improve 'Windows(workbook.xls).Activate" statement

[Hank]

Hello,

I have a VBA routine that creates an xy data plot on worksheet (A) and
displays a regression fit in the chart textbox. I need to have the
chart drawn to force visual inspection of the data, and would like to
be able to copy the regression text that is displayed in the text box
on the on the chart to a cell on a new worksheet (B) in the same
workbook. The routine needs to be generic so that the code will run on
any workbook. Using the macro recorder, I get the code below. The
problem, for me, is to make the "Windows("Workbook1.xls").Activate"
statement generic. Using statements such as(Windows("0").Activate have
produced strange, and unpredicatebe results.

Thanks for any suggestions!

Hank

Sub Copy_Text()
'
' '
Sheets("A").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select
ActiveWindow.Visible = False
Windows("Workbook1.xls").Activate
Sheets("B").Select
Range("A1").Select
ActiveSheet.Paste
End Sub

 

[MattShoreson]

You try creating an excel addin (xla) which can be incorportated as part
of an excel instance.

Then you can run the macro using the reference 'activeworkbook '.

it's a little more complicated but there's plenty of information about
creating them on excelforum.com