VBA: Find Match and Paste Data

Nordicrx8

Board Regular
Joined
Dec 10, 2015
Messages
143
Office Version
  1. 365
Platform
  1. Windows
Hey All! Happy Friday-Eve!

The below code is working perfectly to add new records to a workbook, but I would like to alter it to have the ability to UPDATE an EXISITING record.

So rather than it looking for the first empty row, I need it to find a match for a record, then paste in the data (same as below) starting in row of the first match it found.

Record to search for: Sch_Pricing tab in cell B1
Where to search for record: Main tab in col A
Data to paste: same data as below
Where to paste: Starting in the first row of the matching data

I hope this provides enough information, but please let me know if you need more! Thanks so much for your help on this! ?

VBA Code:
Private Sub Update_Master_Click()

With Sheets("Main")

Dim FirstEmptyRow As Long
FirstEmptyRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row

Sheets("Sch_Pricing").Range("B2:M16").Copy
.Cells(FirstEmptyRow, "A").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

Sheets("Sch_Pricing").Range("B17:M37").Copy
.Cells(FirstEmptyRow, "FJ").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

Excel.Application.CutCopyMode = False

End With
End Sub
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Hoping that I managed to understand your request, try this. Eventually change as needed.
VBA Code:
Private Sub Update_Master()
    Dim LR     As Long                            'last row
    Dim RF     As Long                            'record found row
    With Sheets("Main")
        LR = .Cells(.Rows.Count, "A").End(xlUp).Row
        RF = .Range("A2:A" & LR).Find(What:=Sheets("Sch_Pricing").Range("B1")).Row
        Sheets("Sch_Pricing").Range("B2:M16").Copy
        .Cells(RF, "A").PasteSpecial Paste:=xlPasteValues 'paste to row where record B1 was found
        Sheets("Sch_Pricing").Range("B17:M37").Copy
        .Cells(RF, "FJ").PasteSpecial Paste:=xlPasteValues 'paste to row where record B1 was found
        Application.CutCopyMode = False
    End With
End Sub
 
Upvote 0
Solution
Hoping that I managed to understand your request, try this. Eventually change as needed.
VBA Code:
Private Sub Update_Master()
    Dim LR     As Long                            'last row
    Dim RF     As Long                            'record found row
    With Sheets("Main")
        LR = .Cells(.Rows.Count, "A").End(xlUp).Row
        RF = .Range("A2:A" & LR).Find(What:=Sheets("Sch_Pricing").Range("B1")).Row
        Sheets("Sch_Pricing").Range("B2:M16").Copy
        .Cells(RF, "A").PasteSpecial Paste:=xlPasteValues 'paste to row where record B1 was found
        Sheets("Sch_Pricing").Range("B17:M37").Copy
        .Cells(RF, "FJ").PasteSpecial Paste:=xlPasteValues 'paste to row where record B1 was found
        Application.CutCopyMode = False
    End With
End Sub
Hey Rollis!

Thanks so much - this got me on the right path! really appreciate your help!! Final code is below incase anyone needs it.

Also quick question: I want to make this dynamic, instead of it pointing to a hard coded range:

VBA Code:
Sheets("Sch_Pricing").Range("B2:K16").Copy

I have values for the range housed in cells W5 and X5 on a tab called "LOV", but I'm not sure how to update the formula to get the value from these cells to make the range dynamic. I tried a few different ways, but I keep getting errors. If you have any thoughts, I'd appreciate it! :)

Thanks so much for your help again! Happy Friday!

VBA Code:
Sub Update_Existing()
'
    Dim RF     As Long
    With Sheets("Main")
    
        RF = .Range("A2:A10000").Find(What:=Sheets("Sch_Pricing").Range("B1")).Row
        
        Sheets("Sch_Pricing").Range("B2:K16").Copy
        .Cells(RF, "A").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        
        Sheets("Sch_Pricing").Range("B17:K47").Copy
        .Cells(RF, "FJ").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        
        Sheets("Material").Range("B4:K152").Copy
        .Cells(RF, "T").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
         
        Application.CutCopyMode = False
        
    End With
End Sub
 
Upvote 0
Sorry "post cancelled", wrong solution given.
 
Upvote 0
If these cells in sheet LOV contain the cell coordinates to determine the dynamic range (example: C4 and M18) you can change the line in the macro to:
VBA Code:
Sheets("Sch_Pricing").Range(Sheets("LOV").Range("W5") & ":" & Sheets("LOV").Range("X5")).Copy
 
Upvote 0
If these cells in sheet LOV contain the cell coordinates to determine the dynamic range (example: C4 and M18) you can change the line in the macro to:
VBA Code:
Sheets("Sch_Pricing").Range(Sheets("LOV").Range("W5") & ":" & Sheets("LOV").Range("X5")).Copy
that worked perfectly, and now I see what I was doing wrong in my attempts! thanks so much again - I appreciate you!
 
Upvote 0
Thanks for the positive feedback(y), glad having been of some help.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top