M
Mel
After Macro runs, no cell activated - what am I missing?
I have a print macro that hides blank rows, copies cell F5 to the left
header, prints, unhides blank rows.
It works great but when it finnishes running, I want it to return to
cell F5 (merged cells F5, G5 & H5) but
what happens is it ends up showing cell F5 in the name box but cell f5
is not highlighted as open. I need to
select with the mouse cell F5. I know this is not a big thing but
users may not do that and think that the
spreadsheet is stuck.
How can I select and activate cell F5 or what is causing it to not
select?
I have tried changing the last row from 'Range("f5").Select' to
'Range("f5").Activate' but no luck.
Once the macro runs, I must use the mouse to select a cell to use the
sheet. (normally the last cell selected is active, alowing the user
to type in any changes needed.
thx (macro is listed below)
Mel
'*********************************************
Sub Print_Page()
'
' Print_Page Macro
' Macro recorded 18/12/2011 by Pilgrim
'
'Sub HideEmptyRows()this is the macro to hide blank rows before
printing
Dim LastRow As Long
Dim Rng As Range
Dim RngEnd As Range
Set Rng = Worksheets("RATE-REVISION").Range("E9")
Set RngEnd = Rng.Parent.Cells(Rows.Count, Rng.Column).End(xlUp)
Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Rng.Parent.Range(Rng,
RngEnd))
LastRow = Rng.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious,
False).Row
If LastRow < RngEnd.Row Then
Rng.Parent.Range(Rows(LastRow + 1), Rows(RngEnd.Row)).Hidden = True
End If
'End Sub End of the macro to hide blank rows
'*********************************************
'to copy cell F5 (name) into left header
Application.ScreenUpdating = False
'assuming the info is in cell f5
Header = Range("f5").Value
With ActiveSheet.PageSetup
'Set Font, Font Attribute and Font Size here if you'd like
myset = "&""Tahoma,Italic""&9"
..LeftHeader = Header
End With
'*********************************************
'Print setup
Range("A1
90").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$p$90"
ActiveWindow.SelectedSheets.PrintOut Copies:=1
'Range("b1").Select
'*********************************************
'Sub ShowAllRows()This is the macro that un-hides the rows previously
hidden prior to printing
On Error GoTo ExitOut
With Worksheets("RATE-REVISION")
Set Rng = .Columns(1).Cells.SpecialCells(xlCellTypeVisible)
StartRow = Rng.Areas(1).Rows.Count
EndRow = Rng.Areas(2).Row
..Range(Rows(StartRow), Rows(EndRow)).EntireRow.Hidden = False
End With
ExitOut:
'*********************************************
Application.ScreenUpdating = True
Range("a1").Select
Range("f5").Select
End Sub
I have a print macro that hides blank rows, copies cell F5 to the left
header, prints, unhides blank rows.
It works great but when it finnishes running, I want it to return to
cell F5 (merged cells F5, G5 & H5) but
what happens is it ends up showing cell F5 in the name box but cell f5
is not highlighted as open. I need to
select with the mouse cell F5. I know this is not a big thing but
users may not do that and think that the
spreadsheet is stuck.
How can I select and activate cell F5 or what is causing it to not
select?
I have tried changing the last row from 'Range("f5").Select' to
'Range("f5").Activate' but no luck.
Once the macro runs, I must use the mouse to select a cell to use the
sheet. (normally the last cell selected is active, alowing the user
to type in any changes needed.
thx (macro is listed below)
Mel
'*********************************************
Sub Print_Page()
'
' Print_Page Macro
' Macro recorded 18/12/2011 by Pilgrim
'
'Sub HideEmptyRows()this is the macro to hide blank rows before
printing
Dim LastRow As Long
Dim Rng As Range
Dim RngEnd As Range
Set Rng = Worksheets("RATE-REVISION").Range("E9")
Set RngEnd = Rng.Parent.Cells(Rows.Count, Rng.Column).End(xlUp)
Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Rng.Parent.Range(Rng,
RngEnd))
LastRow = Rng.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious,
False).Row
If LastRow < RngEnd.Row Then
Rng.Parent.Range(Rows(LastRow + 1), Rows(RngEnd.Row)).Hidden = True
End If
'End Sub End of the macro to hide blank rows
'*********************************************
'to copy cell F5 (name) into left header
Application.ScreenUpdating = False
'assuming the info is in cell f5
Header = Range("f5").Value
With ActiveSheet.PageSetup
'Set Font, Font Attribute and Font Size here if you'd like
myset = "&""Tahoma,Italic""&9"
..LeftHeader = Header
End With
'*********************************************
'Print setup
Range("A1
90").SelectActiveSheet.PageSetup.PrintArea = "$A$1:$p$90"
ActiveWindow.SelectedSheets.PrintOut Copies:=1
'Range("b1").Select
'*********************************************
'Sub ShowAllRows()This is the macro that un-hides the rows previously
hidden prior to printing
On Error GoTo ExitOut
With Worksheets("RATE-REVISION")
Set Rng = .Columns(1).Cells.SpecialCells(xlCellTypeVisible)
StartRow = Rng.Areas(1).Rows.Count
EndRow = Rng.Areas(2).Row
..Range(Rows(StartRow), Rows(EndRow)).EntireRow.Hidden = False
End With
ExitOut:
'*********************************************
Application.ScreenUpdating = True
Range("a1").Select
Range("f5").Select
End Sub