Returning to the active cell

[MattNewby]

Hi,


I have wrote a very simple macro and it isn't quiet working. What I am trying to do is as follows:

• Store the active cell as a variable to be used later
• Past special values in a column on Saving
• Return to the cell that was active before the macro was run

My code is as follows:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ScreenUpdating = False

Dim mycel As Integer
mycel = ActiveCell
Sheets(1).Range("C:C").Copy
Sheets(1).Range("C:C").PasteSpecial xlPasteValues
Range("mycel").Select

ScreenUpdating = True
End Sub

I think it's just the bit in red that's wrong. If any one can advise it will be appreciated.

Thanks

Matt

 

[dfenton21]

Try removing the quotes in the red line and change
mycel = ActiveCell
to
mycel = ActiveCell.address

 

[shg]

Not sure why you need that; you aren't changing the selection.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    With Worksheets(1).Range("C:C")
        .Value = .Value
    End With
End Sub

 

[MattNewby]

This is just a bit of test code to illustrate what I am trying to do.

When people enter to a shared spreadsheet I have set it up, so the user ID and date is entered into any blank cells in next to a set of populated cells. What I want to do is pastespecial these formulas and return the user to the cell they last had selected.

Hope this clafifys my question

Matt

 

[MattNewby]

Nope does not work. It buggs just like the first line of code.

I am using excel 2003 if that makes a difference.

Matt

 

[jasonb75]

If you use the code that shg has given you then it does just that without leaving the cell that the user is in so there is no need to re-activate it.

However I have a feeling that using Range("C:C") could leave you with a lot of dirty cells.

Maybe it would be better to restrict it to the used range only.

With Sheets(1).Range("C1:C" & Cells(Rows.Count, "C").End(xlUp).Row)
        .Value = .Value
End With

edit:-

you really don't need this but the correct way would be

Dim mycell As Range
Set mycell = Activecell
 
' your code here
 
mycell.Select

 

[MattNewby]

Appologies,

I was not understanding, didn't realise you could use a with loop that way.

Err, yes it works fine.

Thanks

Matt