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

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

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!