After Macro runs, no cell activated - what am I missing?

  • Thread starter Thread starter Mel
  • Start date Start date
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:p90").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
 
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:p90").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
Click to expand...

Solved... problem was related to 6 cells merged together arounce cell
A1. Once I unmerged and then reformatted so that only 2 cells were
merged, it works.
 
Back
Top