Q: Excel VBA - worksheet/range reference does not work properly I have problem with the worksheet/range initiating and I can't see why my code doesn't work. I was debugging and it seems like I had to be on a certain worksheet to make the related references to work. Can anyone please let me know what did I do wrong?
Sub salesImport()

Application.ScreenUpdating = False

'Excel workbook, the source and target worksheets, and the source and target ranges.
Dim wbBook As Workbook
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Dim rnSource As Range
Dim rnTarget As Range
Dim rng As Range
Dim cIndex, rIndex1, rIndex2, rIndex3, iR, iC As Integer
Dim rowC, columnC As Integer


'Initialize the Excel objects
Set wbBook = ThisWorkbook
With wbBook
    Set wsSource = .Worksheets("Sales")
    Set wsTarget = .Worksheets("Summary-Official")
End With


'On the source worksheet, set the range to the data stored
With wsSource
    rowC = .Cells.SpecialCells(xlCellTypeLastCell).row
    columnC = .Range("A1").SpecialCells(xlCellTypeLastCell).Column
    Set rnSource = .Range(Cells(1, 1), Cells(rowC, columnC))
End With

With wsTarget
    Set rnTarget = .Range("B98:AM122")
End With

rIndex1 = 6 'month
rIndex2 = 10 'plant
rIndex3 = 17 'sales
iR = 0
iC = 0

For Each Column In rnSource
    Column.Cells(rIndex1, 1).Select
    Dim m As String: m = Column.Cells(rIndex1, 1).Value
    Select Case Month(DateValue("01 " & m & " 2012"))
        Case 1
            iC = 6
        Case 2
            iC = 7
        Case 3
            iC = 8
        Case 4
            iC = 9
    End Select
    iR = findrow2(Column.Cells(rIndex2, 1), rnTarget)
    If iR <> 0 Then
        rnTarget.Cells(iR - 97, iC).Value = Column.Cells(rIndex3, 1).Value
    End If
    'MsgBox ("got here")
Next Column

Application.ScreenUpdating = True

End Sub

Thanks!

A: With wsSource
    '...
    Set rnSource = .Range(Cells(1, 1), Cells(rowC, columnC))
End With

should be
With wsSource
    '...
    Set rnSource = .Range(.Cells(1, 1), .Cells(rowC, columnC))
End With

...otherwise your Cells() calls default to the activesheet, which may not be wsSource
