VBA - How to refer to a different worksheet cell

[ez08mba]

I'm having problems in editing my macro to refer to a different worksheet I referenced in while creating my macro. Below is my question with the code, which I thought I could paste in as an image and could not.

' QUESTION:
' "='Jan 16-Jan 22'!RC[-1]" -- This is the name of the sheet to the right of the sheet
' my macro creates and references. This sheet is the 6th sheet, and the sheet my macro
' creates will always be the 5th sheet.
' How do I change this code (i.e. "='Jan 16-Jan 22'!RC[-1]") to reference the 6th worksheet
' every time the 5th is created since the tab name of the 6th sheet will be different every time?

Range("C2").Select
ActiveCell.FormulaR1C1 = "='Jan 16-Jan 22'!RC[-1]"
Range("C3").Select
ActiveCell.FormulaR1C1 = "='Jan 16-Jan 22'!RC[-1]"
Range("C3").Select
Selection.AutoFill Destination:=Range("C3:C20"), Type:=xlFillDefault
Range("C3:C20").Select
Range("C5:C20").Select
Selection.NumberFormat = "General"

 

[Andrew Poulsom]

.Cells(3, .Columns.Count) 'references the last column on row 3 (IV in Excel 2003)

.End(xlToLeft).Column 'returns the number of the first column to the left that is not empty

If that's column 10 we need to deduct 2 (B3's column number) to get the offset of 8 in the formula.