copy paste range after search

Question

I inherited a report that acts as a master tracker for changes made in several other reports. The previous owner was using VLOOKUP formulas to pull data, however I decided to try my hand at a VBA macro and was able to patchwork something together (see below) despite being new to this. It works fine, however:

Problem 1: Right now, the macro returns values one column at a time. I have four nearly identical sub procedures that run back-to-back, one per column: Updated_LName (N), New_Doc (O), New_ID (P), Comments (Q).

Desired Solution: I'd like to increase efficiency by searching for the Student_ID_Nbr (K) in the previous worksheet and then returning values for all four columns at once: Updated_LName, New_Doc, New_ID, Comments (N:Q).

Problem 2: I couldn't figure out a better way than clearing formatting to skip to the next Student_ID_Nbr if it's not found on the previous page. I don't want to overwrite any existing values, which is why I didn't assign an empty string value in the If/Else statement.

Desired Solution: Find a better way to skip to the next iteration if Student_ID_Nbr is not found in the previous worksheet without overwriting existing data.

Problem 3: The macro currently ignores records where there is no Student_ID_Nbr match between the master and the previous worksheet. All changes must be extracted from the other reports and reflected in the master worksheet.

Desired Solution: I'd like to paste in the entire row (A:Q) for each record where the Updated_LName (N), New_Doc (O), New_ID (P), AND/OR Comments (Q) are not blank, AND the Student_ID_Nbr is not present in the master worksheet.

Note: Assume Student_ID_Nbr is a primary key.

Screenshot of example worksheets/data, after running macro successfully

	K	L	M	N	O	P	Q
1				Imported	Imported	Imported	Imported
2	Student_ID_Nbr	Qty	LName	Updated_LName	New_Doc	New_ID	Comments
3	123456789	1	Doe	Smith	Transcript	987654321	Marriage cert submitted

Public Sub PullUpdated_LName()
    
    
    'Declarations
    Dim varID As Variant
    Dim wsCurrent As Worksheet
    Dim wsPrevious As Worksheet
    Dim rngSelection As Range
    Dim i As Integer
    
    For i = 3 To 30000
    
    'Initialization
    Set wsCurrent = ActiveSheet
    Set wsPrevious = wsCurrent.Previous
    Set rngSelection = ActiveCell
    
    
    'Error checking--do nothing if not in the correct column
    If Not rngSelection.Column = 14 Then
    
        MsgBox "Please select a cell in column N.", vbCritical + vbOKOnly, "Error"
        Exit Sub
    
    End If
    
    'Search for ID on the previous ws
    Set varID = wsPrevious.Columns(11).Find(What:=wsCurrent.Cells(rngSelection.Row, 11).Value)
    
    'If ID not found, leave existing values alone (including empty cells) on current sheet
    If varID Is Nothing Then
    
        rngSelection.ClearFormats
    
    Else
    
        'Return the value in the appropriate row and column from the previous sheet
        rngSelection.Value = wsPrevious.Cells(varID.Row, 14).Value
    
    End If
    
    'Regardless, move to the next cell
    wsCurrent.Cells(rngSelection.Row + 1, rngSelection.Column).Select
    
    Next i
    
    'Move to next column (to prepare for the next sub)
    wsCurrent.Cells(3, rngSelection.Column + 1).Select
    
    'Run next sub automatically
    PullNew_Doc
    
End Sub

Thank you to ShawnPCooke for the great starting point.

user_4475468 · Answer

You seem to be re-initializing the worksheets each time around the loop, and the limit of 30000 rows to search seems arbitrary and will likely be slow while that searches empty space. Any time you "Select" a cell, you will potentially have the view updating; best to keep everything in Range variables.

This code probably addresses your first two points:

Public Sub Pull_All()

'Declarations
Dim varID As Variant
Dim wsCurrent As Worksheet
Dim wsPrevious As Worksheet
Dim rngSelection As Range
Dim i As Integer

Dim search_zone As Range
Dim last_row As Long
Dim update_cols As Variant
Dim ref_row As Long
Dim ref_col As Long
Dim col As Variant
update_cols = Array(14, 15, 16, 17)
ref_row = 3
ref_col = 11

'Initialize sheets
Set wsCurrent = ActiveSheet
Set wsPrevious = wsCurrent.Previous
Set search_zone = wsPrevious.Columns(11)

' start on a defined cell in the worksheet; this may need update
Set rngSelection = wsCurrent.Cells(ref_row, ref_col)
last_row = rngSelection.SpecialCells(xlCellTypeLastCell).Row

For i = ref_row To last_row
    
    ' Search for ID on the previous ws
    Set varID = search_zone.Find(What:=rngSelection.Value)
    
    If Not varID Is Nothing Then
    
        ' Return the values in the appropriate row and update columns from the previous sheet
        For Each col In update_cols
            rngSelection.Offset(0, col - ref_col).Value = wsPrevious.Cells(varID.Row, col).Value
        Next col
    
    End If
    
    ' move to the next cell
    Set rngSelection = rngSelection.Offset(1, 0)

Next i

End Sub

copy paste range after search

1 Answers