Hi All,

I have a book with many sheets. The sheets contain two types of data. The
worksheet names contain either an 'A' or a 'B', e.g. 20210A.txt or 20210B.txt

Is it possible to have a macro that will start at the first worksheet, and
do the following:

Does worksheet name contain 'A'? If yes then run macro1.
If no, then does name contain 'B'? If yes run macro2.
Go to next worksheet.
End when no more sheets.

The macros are to plot charts of the data on that sheet, but as the data on
A and B sheets are for different graphs they need different macros.

Any help much appreciated.

Cheers,

Dan

Hi Dr Dan

The code below will cycle through every worksheet in your workbook and if
the last sheet name letter before the .xls is an a or a b it will fire a
message box. You should replace this with the call to the macro you want to
execute.

Mike

Sub dontblameme()
Dim sht As Worksheet
For Each sht In Sheets
Name = sht.Name
Length = Len(Name)
If Mid(Name, Length - 4, 1) = "a" Then
MsgBox (Name) 'or call macro a
ElseIf Mid(Name, Length - 4, 1) = "b" Then
MsgBox (Name) ' or call macro b
End If

Next sht
End Sub

"Dr Dan" wrote:

Hi All,

I have a book with many sheets. The sheets contain two types of data. The
worksheet names contain either an 'A' or a 'B', e.g. 20210A.txt or 20210B.txt

Is it possible to have a macro that will start at the first worksheet, and
do the following:

Does worksheet name contain 'A'? If yes then run macro1.
If no, then does name contain 'B'? If yes run macro2.
Go to next worksheet.
End when no more sheets.

The macros are to plot charts of the data on that sheet, but as the data on
A and B sheets are for different graphs they need different macros.

Any help much appreciated.

Cheers,

Dan

Hi, Dan-
This code will do it:

Sub AllTabs()
Dim SheetName

For Each SheetName In Sheets
If InStr(1, SheetName.Name, "A") 0 Then
Macro_A
goto ReStart:
end if

If InStr(1, SheetName.Name, "B") 0 Then
Macro_B
goto ReStart:
end if

ReStart:
Next SheetName
End Sub

Substitute your macro names for Macro_A and Macro_B.

This code will run against ALL tabs in the workbook, and hidden tabs
may cause a runtime error.

Dave O

I tried it but and it looked very busy for a while. Once finishhed I realised
it had plotted 144 graphs all in the same, first worksheet, using either
macro A or B to give 2 different formattings amongst the plots. The remaining
sheets had no plots on them.

It looks as though it examined all the tab names but did not actually move
into each sheet and look at new data.

Cheers,

Dan

Here's the code I used...
Sub plotallsheets()
'
' plotallsheets Macro
' Macro recorded 23/02/2007 by Dan
'
' Keyboard Shortcut: Ctrl+l


Dim SheetName

For Each SheetName In Sheets
If InStr(1, SheetName.Name, "A") 0 Then
plotchronoamperometry
GoTo ReStart:
End If

If InStr(1, SheetName.Name, "B") 0 Then
plotIVcurve
GoTo ReStart:
End If

If InStr(1, SheetName.Name, "C") 0 Then
plotchronoamperometry
GoTo ReStart:
End If

ReStart:
Next SheetName
End Sub




"DaveO" wrote:

Hi, Dan-
This code will do it:

Sub AllTabs()
Dim SheetName

For Each SheetName In Sheets
If InStr(1, SheetName.Name, "A") 0 Then
Macro_A
goto ReStart:
end if

If InStr(1, SheetName.Name, "B") 0 Then
Macro_B
goto ReStart:
end if

ReStart:
Next SheetName
End Sub

Substitute your macro names for Macro_A and Macro_B.

This code will run against ALL tabs in the workbook, and hidden tabs
may cause a runtime error.

Dave O

Hi Mike,

I've just tried DaveO's code and it behaved strangely. I replied with the
problem. Does your code require all the names to be the same length to
identify the A or B? The tabs vary in length with systematic names taken from
experimental parameters. For example a series of experiements led to the
folllowing names:

19205A.txt 19205B.txt 19210A.txt 19210B.txt 19220A.txt 19220B.txt 19250A.txt
19250B.txt 192100A.txt 192100B.txt 192200A.txt 192200B.txt

I have a set of sheets named the above for each of 10s of samples. As you
can see the last four have an extra digit. Will this cause a problem or does
it look backwards from the end?

Cheers,

Dan

"Mike" wrote:

Hi Dr Dan

The code below will cycle through every worksheet in your workbook and if
the last sheet name letter before the .xls is an a or a b it will fire a
message box. You should replace this with the call to the macro you want to
execute.

Mike

Sub dontblameme()
Dim sht As Worksheet
For Each sht In Sheets
Name = sht.Name
Length = Len(Name)
If Mid(Name, Length - 4, 1) = "a" Then
MsgBox (Name) 'or call macro a
ElseIf Mid(Name, Length - 4, 1) = "b" Then
MsgBox (Name) ' or call macro b
End If

Next sht
End Sub

"Dr Dan" wrote:

Hi All,

I have a book with many sheets. The sheets contain two types of data. The
worksheet names contain either an 'A' or a 'B', e.g. 20210A.txt or 20210B.txt

Is it possible to have a macro that will start at the first worksheet, and
do the following:

Does worksheet name contain 'A'? If yes then run macro1.
If no, then does name contain 'B'? If yes run macro2.
Go to next worksheet.
End when no more sheets.

The macros are to plot charts of the data on that sheet, but as the data on
A and B sheets are for different graphs they need different macros.

Any help much appreciated.

Cheers,

Dan

Hi Mike,

Thanks for the code. Can't get it to run though. Here's what I was using...

Sub plotallsheets2()
'
' Macro14 Macro
' Macro recorded 23/02/2007 by Dan
'
' Keyboard Shortcut: Ctrl+m
'

Dim sht As Worksheet
For Each sht In Sheets
Name = sht.Name
Length = Len(Name)
If Mid(Name, Length - 4, 1) = "a" Then
plotchronoamperometry
ElseIf Mid(Name, Length - 4, 1) = "b" Then
plotIVcurve
ElseIf Mid(Name, Length - 4, 1) = "c" Then
plotchronoamperometry
End If

Next sht
End Sub

Dr Dan,

Sorry I misunderstood your requirement. adding the line
Worksheets(Name).Select
makes the macro select each sheet in turn in addition to evaluating the name

Sub dontblameme()
Dim sht As Worksheet
For Each sht In Sheets

Name = sht.Name
Worksheets(Name).Select

Length = Len(Name)
If Mid(Name, Length - 4, 1) = "a" Then
MsgBox (Name) 'or call macro a
ElseIf Mid(Name, Length - 4, 1) = "b" Then
MsgBox (Name) ' or call macro b
End If

Next sht
End Sub
"Dr Dan" wrote:

I tried it but and it looked very busy for a while. Once finishhed I realised
it had plotted 144 graphs all in the same, first worksheet, using either
macro A or B to give 2 different formattings amongst the plots. The remaining
sheets had no plots on them.

It looks as though it examined all the tab names but did not actually move
into each sheet and look at new data.

Cheers,

Dan

Here's the code I used...
Sub plotallsheets()
'
' plotallsheets Macro
' Macro recorded 23/02/2007 by Dan
'
' Keyboard Shortcut: Ctrl+l


Dim SheetName

For Each SheetName In Sheets
If InStr(1, SheetName.Name, "A") 0 Then
plotchronoamperometry
GoTo ReStart:
End If

If InStr(1, SheetName.Name, "B") 0 Then
plotIVcurve
GoTo ReStart:
End If

If InStr(1, SheetName.Name, "C") 0 Then
plotchronoamperometry
GoTo ReStart:
End If

ReStart:
Next SheetName
End Sub




"DaveO" wrote:

Hi, Dan-
This code will do it:

Sub AllTabs()
Dim SheetName

For Each SheetName In Sheets
If InStr(1, SheetName.Name, "A") 0 Then
Macro_A
goto ReStart:
end if

If InStr(1, SheetName.Name, "B") 0 Then
Macro_B
goto ReStart:
end if

ReStart:
Next SheetName
End Sub

Substitute your macro names for Macro_A and Macro_B.

This code will run against ALL tabs in the workbook, and hidden tabs
may cause a runtime error.

Dave O