Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
run on each sheet after
on each sheet in workbook after sheet "oranges", run macro "apples"
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
run on each sheet after
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. "J.W. Aldridge" wrote: on each sheet in workbook after sheet "oranges", run macro "apples" . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
run on each sheet after
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" wrote: on each sheet in workbook after sheet "oranges", run macro "apples" . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
run on each sheet after
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
run on each sheet after
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. "J.W. Aldridge" wrote: 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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
run on each sheet after
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. -- Cheers, Ryan "J.W. Aldridge" wrote: 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 . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
run on each sheet after
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
run on each sheet after
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. "J.W. Aldridge" wrote: 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 . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
run on each sheet after
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. "Mike H" wrote: 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. "J.W. Aldridge" wrote: 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 . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
run on each sheet after
I'z learning...
it worked. thanx for all the help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel sheet bootom half sheet goes behind top part of sheet | Excel Worksheet Functions | |||
Duplicate sheet, autonumber sheet, record data on another sheet | Excel Worksheet Functions | |||
Copying cells from on sheet to another sheet (via sheet module) | Excel Programming | |||
relative sheet references ala sheet(-1)!B11 so I can copy a sheet. | Excel Discussion (Misc queries) | |||
Inserting a row in sheet A should Insert a row in sheet B, removing a row in Sheet A should remove the corresponding row in sheet B | Excel Programming |