kathyb0527
02-22-2011, 01:29 PM
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!
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!