Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro running within Macro
I have a macro which selects to the last field within a given range
which has data greater than zero. I have tried to use this macro in another macro to reselect the range and set print range for printing. After running the macro the first time, it hard codes the set print range and will not change it after that. I need the Application.Run "'09RadLabPath.xls'!Sheet3.find_it" in the code below to run each time and then have the set print range be updated which might be different than the $AM$34. I am also including the code for the Find It macro at the end. Please let me know if you can help resolve this issue. Thanks! Randy Sub RLP802PrintServices() ' ' RLP802PrintServices Macro ' Macro recorded 4/4/2008 by ' ' ActiveWorkbook.CustomViews("RLP-802 Print Services View").Show Application.Run "'09RadLabPath.xls'!Sheet3.find_it" ActiveSheet.PageSetup.PrintArea = "$D$12:$AM$34" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Application.Run "'09RadLabPath.xls'!RLP802OriginalView" End Sub Sub find_it() endcell = 0 Set rGoTo = Range("D12") Set r = Range("D12:AN112") For Each rr In r If rr.Value = 0 Then Else endcell = rr.Address Set rGoTo = rr End If Next Range("D12:" & endcell).Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro running within Macro
Randy,
Try it this way: Sub RLP802PrintServices() ActiveWorkbook.CustomViews("RLP-802 Print Services View").Show ActiveSheet.PageSetup.PrintArea = find_it ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Application.Run "'09RadLabPath.xls'!RLP802OriginalView" End Sub Function find_it() As String Dim rGoTo As Range Dim r As Range Dim rr As Range Set rGoTo = Range("D12") Set r = Range("D12:AN112") For Each rr In r If rr.Value < 0 Then If rr.Column rGoTo.Column Then Set rGoTo = Cells(rGoTo.Row, rr.Column) If rr.Row rGoTo.Row Then Set rGoTo = Cells(rr.Row, rGoTo.Column) End If Next find_it = Range("D12", rGoTo).Address End Function HTH, Bernie MS Excel MVP wrote in message ... I have a macro which selects to the last field within a given range which has data greater than zero. I have tried to use this macro in another macro to reselect the range and set print range for printing. After running the macro the first time, it hard codes the set print range and will not change it after that. I need the Application.Run "'09RadLabPath.xls'!Sheet3.find_it" in the code below to run each time and then have the set print range be updated which might be different than the $AM$34. I am also including the code for the Find It macro at the end. Please let me know if you can help resolve this issue. Thanks! Randy Sub RLP802PrintServices() ' ' RLP802PrintServices Macro ' Macro recorded 4/4/2008 by ' ' ActiveWorkbook.CustomViews("RLP-802 Print Services View").Show Application.Run "'09RadLabPath.xls'!Sheet3.find_it" ActiveSheet.PageSetup.PrintArea = "$D$12:$AM$34" ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Application.Run "'09RadLabPath.xls'!RLP802OriginalView" End Sub Sub find_it() endcell = 0 Set rGoTo = Range("D12") Set r = Range("D12:AN112") For Each rr In r If rr.Value = 0 Then Else endcell = rr.Address Set rGoTo = rr End If Next Range("D12:" & endcell).Select End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro running within Macro
On Apr 8, 9:46*am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote: Randy, Try it this way: Sub RLP802PrintServices() * *ActiveWorkbook.CustomViews("RLP-802 Print Services View").Show * *ActiveSheet.PageSetup.PrintArea = find_it * *ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True * *Application.Run "'09RadLabPath.xls'!RLP802OriginalView" End Sub Function find_it() As String Dim rGoTo As Range Dim r As Range Dim rr As Range Set rGoTo = Range("D12") Set r = Range("D12:AN112") For Each rr In r * * If rr.Value < 0 Then * * * * If rr.Column rGoTo.Column Then Set rGoTo = Cells(rGoTo..Row, rr.Column) * * * * If rr.Row rGoTo.Row Then Set rGoTo = Cells(rr.Row, rGoTo.Column) * * End If Next find_it = Range("D12", rGoTo).Address End Function HTH, Bernie MS Excel MVP wrote in message ... I have a macro which selects to the last field within a given range which has data greater than zero. *I have tried to use this macro in another macro to reselect the range and set print range for printing. After running the macro the first time, it hard codes the set print range and will not change it after that. *I need the Application.Run "'09RadLabPath.xls'!Sheet3.find_it" in the code below to run each time and then have the set print range be updated which might be different than the $AM$34. *I am also including the code for the Find It macro at the end. *Please let me know if you can help resolve this issue. Thanks! Randy Sub RLP802PrintServices() ' ' RLP802PrintServices Macro ' Macro recorded 4/4/2008 by ' ' * *ActiveWorkbook.CustomViews("RLP-802 Print Services View").Show * *Application.Run "'09RadLabPath.xls'!Sheet3.find_it" * *ActiveSheet.PageSetup.PrintArea = "$D$12:$AM$34" * *ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True * *Application.Run "'09RadLabPath.xls'!RLP802OriginalView" End Sub Sub find_it() endcell = 0 Set rGoTo = Range("D12") Set r = Range("D12:AN112") For Each rr In r * *If rr.Value = 0 Then * *Else * * * *endcell = rr.Address * * * *Set rGoTo = rr * *End If Next Range("D12:" & endcell).Select End Sub- Hide quoted text - - Show quoted text - That worked perfectly!!! Thanks a bunch Bernie! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Different results when running macro vs stepping into macro | Excel Programming | |||
Report with macro losing links to a particular worksheet after running macro | Excel Programming | |||
disable user running macro from Tools Macro | Excel Discussion (Misc queries) | |||
Need syntax for RUNning a Word macro with an argument, called from an Excel macro | Excel Programming | |||
Launch Macro in Access via Macro running in Excel??? | Excel Programming |