How do i copy values from the previous sheet?

[JumboCactuar]

Hi,
i have the following

ActiveCell.Offset(0, 0).Range("A1:C500").Value2 = ActiveCell.Offset(0, 0).Range("A1:C500").Value2

how do i modify this to copy values from previous sheet instead of current into same range

if it helps, the sheets are labelled as weekly dates (dd-mm)
26-11
03-12
10-12
17-12
24-12

the tabs are in the appropriate order though so i dont think names matter

appreciate any help

 

[jolivanes]

Maybe (Change address references as required)

ActiveSheet.Range("A1:C4").Value = Sheets(ActiveSheet.Index - 1).Range("A1:C4").Value

 

[JumboCactuar]

The above did nothing

i tried like so:

ActiveCell.Offset(0, 0).Range("A1:C500").Value2 = ActiveCell.Offset(0, 0).Sheets(ActiveSheet.Index - 1).Range("A1:C500").Value2

and i get object doesnt support this property or method.

 

[sassriverrat]

Here ya go- put this in a module and then use "=PrevSheet('Cell from Previous Page) in a formula.

Function PrevSheet(RCell As Range)
    Dim xIndex As Long
    Application.Volatile
    xIndex = RCell.Worksheet.Index
    If xIndex > 1 Then
        PrevSheet = Worksheets(xIndex - 1).Range(RCell.Address)
    End If
End Function

 

[jolivanes]

That's not the same code as in Post #2 .

 

[JumboCactuar]

That's not the same code as in Post #2 .

As I said I tried your code and nothing happens though no error

 

[jolivanes]

Which cells from the previous sheet do you want to copy/paste?

 

[jolivanes]

This will probably do what you have in mind.

Dim a As String
a = ActiveCell.Offset(0, 0).Range("A1:C500").Address
ActiveSheet.Range(a).Value2 = Sheets(ActiveSheet.Index - 1).Range(a).Value2

 

[sassriverrat]

Did you try my function piece? I know mine works- I have it working in an active workbook now. It will only reference the previous sheet to the activesheet.

 

[JumboCactuar]

@sassriverrat
Thanks, the function works great and definitely one I will save for future stuff
@jolivanes not tried your code yet, but what I wanted to copy was the 3 columns from active cell

I don't know where the A1:C500 comes from? Probably because of relative referencing. It's what come from the macro recorder.