Range setting incorrectly

[kathyb0527]

I've written code to pull information from one excel workbook to another. The code works, but I had to activate the original workbook in order to get my ranges to count correctly. I know that wsClientReport has 1568 rows and wbSourcefile has 1450. Yet, if I don't activate wsClientReport prior to the clean line, my range is only 1450. Can someone please look at my code and explain why that is happening? My data is all confidential so I would have to recreate the sheets if they are needed.

Sub MatchStudy()
Dim stFullName As String
Dim wbSourcefile As Workbook
Dim stShortName As String
Dim wsClientReport As Worksheet
Dim rShipCol As Range, rWatID As Range, c As Range, rVtable

Set wsClientReport = ActiveSheet
Set rShipCol = wsClientReport.Range("M3:M" & Range("A" & Rows.Count).End(xlUp).Row)

stFullName = Application.GetOpenFilename
stShortName = Right$(stFullName, Len(stFullName) - InStrRev(stFullName, "\"))
If stShortName = "False" Then
Exit Sub
End If
On Error Resume Next
Set wbSourcefile = Workbooks(stShortName)
On Error GoTo 0
If wbSourcefile Is Nothing Then
Set wbSourcefile = Workbooks.Open(stFullName)
End If

Set rWatID = wbSourcefile.Worksheets("Sheet1").Range("A1:Z" & Range("A" & Rows.Count).End(xlUp).Row)
Set rVtable = wbSourcefile.Worksheets("Sheet1").Range("P3:Z" & Range("P" & Rows.Count).End(xlUp).Row)

For Each c In rWatID
c.Value = WorksheetFunction.Clean(c.Value)
Next c
wsClientReport.Activate

For Each c In rShipCol
c.Offset(0, -7).Value = WorksheetFunction.Clean(c.Offset(0, -7).Value)
Next c

For Each c In rShipCol
c.Value = Application.VLookup(c.Offset(0, -7).Value, rVtable, 7, False)
Next c

wbSourcefile.Close (False)

End Sub


Thank you!

[Bob Phillips]

Try this



With wbSourcefile.Worksheets("Sheet1")

Set rWatID = .Range("A1:Z" & .Range("A" & .Rows.Count).End(xlUp).Row)
end with
With wbSourcefile.Worksheets("Sheet1")

Set rVtable =.Range("P3:Z" & .Range("P" & .Rows.Count).End(xlUp).Row)
End With
For Each c In rWatID
c.Value = WorksheetFunction.Clean(c.Value)
Next c
wsClientReport.Activate