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]

Try:

Sub Test()
    Dim ShName As String
    ShName = Worksheets(ActiveSheet.Index + 1).Name
    With Range("C2:C20")
        .FormulaR1C1 = "='" & ShName & "'!RC[-1]"
        .NumberFormat = "General"
    End With
End Sub

 

[ez08mba]

That worked great!!!

I forgot to ask about a second similar code doing the same thing except the referenced data moves week to week in the same referenced sheet.

In the case below, when my macro creates the new worksheets(5), it also references cells in Worksheets(4) tilted "='YTD Weekly Trend Tickets'!RC[8]". What it is doing is pull data from a range of cells representing the current week. However, when I run my macro a week later, it will need to reference the "next" range of cells to the right to pull that week's data. I know that the "RC[8]" is pulling data 8 Columns over from where I'm referencing. Can I change that so it always pulls the next column the following week, etc? My guess is that it will require and If/Then statement. So,...

What I need it to do every time the macro runs is reference the next range of cells to the right of the previous week. Here is the code as it stands:

' Selection of Current Week's Data'

Range("B2").Select
ActiveCell.FormulaR1C1 = "='YTD Weekly Trend Tickets'!RC[8]"
Range("B3").Select
ActiveCell.FormulaR1C1 = "='YTD Weekly Trend Tickets'!RC[8]"
Range("B3").Select
Selection.AutoFill Destination:=Range("B3:B20"), Type:=xlFillDefault
Range("B3:B20").Select

' Selection of 2nd Particular's Current Week's & Data Labels
'

Range("A3").Select
ActiveCell.FormulaR1C1 = "='YTD Weekly Trend Tickets'!RC"
Range("A3").Select
Selection.AutoFill Destination:=Range("A3:A20"), Type:=xlFillDefault
Range("A3:A20").Select

 

[Andrew Poulsom]

Is the column you want to reference the last used column in the worksheet?

 

[ez08mba]

No. I could make it that way. ??!!??

They are predated date ranges (e.g. Jan 1-Jan 6; Jan 7-Jan 13;....) dragged to the end of the year.

Thanks!

 

[Andrew Poulsom]

But is it the last used column in row 2?

 

[ez08mba]

The date ranges are in Row 2, they go to the end of the year in Row 2
Rows 3-20 are unused rows but have number formatting assigned.

See the screen shot here:
http://imageshack.us/photo/my-images/830/screenshotku.jpg/

Hope this helps!

 

[ez08mba]

In addition to my previous post....

I may have misunderstood and it was too late to edit.

The last column not being used is column BB and Row 1 is not being used at all.

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim LC As Long
    With Worksheets("YTD Weekly Trend Tickets")
        LC = .Cells(3, .Columns.Count).End(xlToLeft).Column
        Range("B2:B20").FormulaR1C1 = "='" & .Name & "'!RC[" & LC - Range("b3").Column & "]"
    End With
End Sub

 

[ez08mba]

Thanks for the help and hard work here. So this is pulling the last column with data. Since I'm new to VBA and attempting formulate this type of solution; and in an effort never to burden you with the same question twice, do you mind talking me through this part?


LC = .Cells(3, .Columns.Count).End(xlToLeft).Column<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Range("B2:B20").FormulaR1C1 = "='" & .Name & "'!RC[" & LC - Range("b3").Column & "]"<o></o>


I think this is what is happening

.Cells(3, .Columns.Count) "This is saying count the columns based on cells in row 3"

.End(xlToLeft).Column "When your count reaches the end, refer to the left column

If I'm correct, then I understand the range and formula bit, but I'm not quite following this:

"='" & .Name & "'!RC[" & LC - Range("b3").Column & "]"

Thanks!