Relative cells in previous sheet

[whvermason]

Hello, could really use some help with something. I am trying to
write an Excel macro that will do the following:
1. Use whatever cell I currently have selected on any given sheet as a
relative cell.
2. Go back one sheet (using PreviousSheet) and copy the data from that
same relative cell.
3. Paste that data into the relative cell on the given sheet I was on.

I have 13 sheets and I want to copy the data from a specific cell on
one sheet to the same cell on the next sheet. The sheets and cells
have to be non-specific in the macro to allow me to run the macro on
any of my 13 sheets and any of the cells on that sheet.

If I record the macro as I do it I end up with the following in VBA:
Range("C12).Select
Sheets("Sheet 3").Select
Range("C12").Select
Selection.Copy
Sheets("Sheet 4").Select
Range("C12").Select
Selection.PasteSpecial Paste:=x1PasteValues, Operation:=x1None,
SkipBlanks _
:=False, Transpose:=False

Obviously, from my explanation, I need to replace the "C12" reference
so the macro just runs from whatever cell I have selected, and the
"Sheet 3" and "Sheet 4" refrences have to be replaced to allow the
macro to run from whatever sheet I'm currently on and go back one
sheet to copy the data. I've been using PreviousSheet which I set up
awhile ago and has worked great for my other macros in this
spreadsheet. I just can't get this one to work. Any help anyone can
offer would be greatly appreciated.

 

[Guest]

Perhaps

Sub test()
Dim lngLast As Long

lngLast = Application.Max(1, ActiveSheet.Index - 1)
With ActiveCell
.Value = Sheets(lngLast).Range(.Address)
End With

End Sub

 

[whvermason]

Perhaps

Sub test()
Dim lngLast As Long

lngLast = Application.Max(1, ActiveSheet.Index - 1)
With ActiveCell
.Value = Sheets(lngLast).Range(.Address)
End With

End Sub








- Show quoted text -

That was perfect! Thank you very much! I have no idea how that works
or what those commands mean - but it does exactly what I needed.
Thanks again.

 

[Guest]

you're welcome.

That was perfect! Thank you very much! I have no idea how that works
or what those commands mean - but it does exactly what I needed.
Thanks again.