run on each sheet after

[J.W. Aldridge]

on each sheet in workbook after sheet "oranges", run macro "apples"

 

[Mike H]

Hi,

Try this

For x = Sheets("Oranges").Index + 1 To Worksheets.Count
Call apples
Next


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Mike H]

On reflection you will have to pass the value of X to sub apples to do
anything so perhaps this slight change

Public x As Long
Sub nn()
For x = Sheets("Oranges").Index + 1 To Worksheets.Count
Call apples
Next
End Sub

Sub apples()
MsgBox Sheets(x).Name
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[J.W. Aldridge]

unfortunately, i couldn't get those to work on more than the first
sheet after the index.
even tried to work it out myself.


Sub do_things()
'
Dim sh As Worksheet

'for each sheet in workbook after "ind templates"
x = Sheets("IND_BRKDWN").Index
For Each sh In ThisWorkbook.Sheets
If sh.Index > x Then
Application.Run "AAA"
End If

Next

End Sub

 

[Mike H]

unfortunately, i couldn't get those to work on more than the first

sheet after the index.

did you use my code as posted, what went wrong?

I've modified your code to work but doing it the way your trying you'll have
problems identifying in code what sheet your up to unless you pass a
parameter to the sub AAA

Sub do_things()
'
Dim sh As Worksheet
'for each sheet in workbook after "ind templates"
x = Sheets("IND_BRKDWN").Index
Stop
For Each sh In ThisWorkbook.Sheets
If sh.Index > x Then
Call AAA
End If
Next

End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Ryan H]

Give this code a try.

Sub do_things()

Dim sh As Long

For sh = Sheets("IND_BRKDWN").Index + 1 To Sheets.Count
Call AAA
Next sh

End Sub

If this code doesn't work then post the code that is in procedure "AAA". It
may be more efficient to integrate the loop into procedure. Hope this helps!
If so, let me know, click "YES" below.

 

[J.W. Aldridge]

ok. i'm not sure why this isnt working, but i truly appreciate all the
help.

in lieu of Ryan's advice, I even tried putting the code in itself
instead of trying to call it.
Didn't work.

Any suggestions?

Sub AAA()
Dim StartCol As Long
Dim EndCol As Long
Dim ColNdx As Long
StartCol = 1 ' column A
EndCol = 28 ' column AB
For ColNdx = EndCol To StartCol Step -1
If Application.CountA(Columns(ColNdx)) = 1 Then
Columns(ColNdx).Delete
End If
Next ColNdx
End Sub

 

[Mike H]

Any suggestions?

Yes. Let's first be clear what this sub does. It reads backwards from Col 28
to Col 1 and if there is only 1 value in the column (Text or numeric) the
column is deleted and I assume that is what you want it do do.

Now returning to your very first post you want to do this on every sheet
AFTER sheet called IND_BRKDWN

So lets try again. Note as I advised in my first post, you have to know
where you are in the code hence I pass the variable X to the sub. Then when
we do the deletion we specify the sheet name by utilising the X variable with
the stetement

Set sht = Sheets(x)


Public x As Long
Sub nn()
For x = Sheets("IND_BRKDWN").Index + 1 To Worksheets.Count
Call AAA
Next
End Sub

Sub AAA()
Dim StartCol As Long
Dim EndCol As Long
Dim ColNdx As Long
Set sht = Sheets(x)
StartCol = 1 ' column A
EndCol = 28 ' column AB
For ColNdx = EndCol To StartCol Step -1
If Application.CountA(sht.Columns(ColNdx)) = 1 Then
sht.Columns(ColNdx).Delete
End If
Next ColNdx
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Mike H]

Just one additional point and from your post

unfortunately, i couldn't get those to work on more than the first

?sheet after the index

That was because you weren't specifying the sheet name so the code was
looping as you wanted but in each iteration of the loop it was working on the
same sheet
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[J.W. Aldridge]

I'z learning...

it worked.

thanx for all the help.