Excel Macro to call to all sheet rather then name

[Nitha120]

Hi,
I have this line,

Sheets("MC1A.txt").Select
Application.Run "'compression test 2-21-05.xls'!Force_Comp_chart"
ActiveWindow.Visible = False
Windows("compression test 2-21-05.xls").Activate
Sheets("MC1B.txt").Select
Application.Run "'compression test 2-21-05.xls'!Force_Comp_chart"
ActiveWindow.Visible = False
Windows("compression test 2-21-05.xls").Activate

How do I replace the "MC1A.txt and MC1B" with a variable that call to a all the worksheet number that work same way without having to repeat the 4lines and the specific name (MC1B.txt ect..).

I try changing
Sheets("MC1A.txt").Select
with sheet(Array("sheet1","sheet2").Select
but its not working

Any help or suggestion is greatly appreciated!

 

[KenWright]

To hit all worksheets in that workbook:-

For x = 1 To ActiveWorkbook.Worksheets.Count
'Do stuff
Next x


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Nitha120]

Hi KenWright,

I did,

Sub graphall()
Dim i As Integer

For i = 1 To ActiveWorkbook.Worksheets.Count
'Do stuff
Application.Run "'compression test 2-21-05.xls'!Force_Comp_chart"
ActiveWindow.Visible = False
Windows("compression test 2-21-05.xls").Activate
Next i
End Sub

what it did was graphs the same chart, 13 time (13 the numbers of worksheet i have) and put in one worksheet.
How can I make it so that the each chart is in each worksheet rather then in one worksheet.

thank you

 

[PHV]

Somewhere should be something like this:
ActiveWorkbook.Worksheets(i).Select

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[KenWright]

Not sure what you have in your other routine, so maybe just try

worksheets(i).activate

after 'Do Stuff (which you don't need)

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[kpetursson]

when you replaced
Sheets("MC1A.txt").Select with
sheet(Array("sheet1","sheet2").Select
^
+-- Notice the missing "s" ??

Kevin Petursson

 

[Nitha120]

Hi guys, here is my code for the routine

Sub Force_Comp_chart()
'declaration of vairable types
Dim chartData As Range
Dim worksheetName As String
' do not change this part deal with selecting the column
' in the spreadsheet while "A8" is the initial starting xaxis values

Range("A8").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

' set chartData base on selection highlight rather then the range
' allow worksheet to be a global variable where any name can be open

Set chartData = Selection
worksheetName$ = ActiveSheet.Name
' need to change the Source and Name as global varialbe
' everything else stay the same

Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.SetSourceData Source:=chartData, PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:=worksheetName$
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Force vs. Compression"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Compression(mm)"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Force(N)"
End With
' Delete the column following SeriesCollection,
' Series Collection are column on spreadsheet
' Must be from large to small (exp 4-1)
ActiveChart.SeriesCollection(4).Select
Selection.Delete
ActiveChart.SeriesCollection(3).Select
Selection.Delete
ActiveChart.SeriesCollection(1).Select
Selection.Delete
End Sub

This routine take the data in the worksheet and graph the compression vs. force. My problem is I have 13 worksheets data and I would like to write a code in a sumary sheet so when i run it, it graph the chart in each worksheet automatically rather have to run the macro again each time for each worksheet.

You guy are very helpful, thank you!

 

[kpetursson]

how about this...

dim i as long
For i = 1 To Sheets.Count
Sheets(i).Select
Force_Comp_chart()
Next i

this should loop throught all of the sheets, and run you chart generator on eact one after it has benn made the current sheet

I think it should work.

Kevin Petursson

 

[Loomah]

Hi
In the interest of skinning a cat as many ways as possible you could also use
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
Next
to activate each worksheet

A little word of warning about Kevin's code above - if you use Sheets, as opposed to Worksheets, the collection includes Chart sheets etc which would cause problems if you try to select a range!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​