Excel VBA : Copy values found in Cells to Sheet and Remove Row

[Newbie123456]

Sub Button3_Click()

'Search Button to Search Serial Number with InStore Table.
If Application.CountIf(Sheets("InStore").Columns(1), Sheets("Deploy Form").Range("C6")) Then
 MsgBox "Serial Number Found"
'Run Save Record 
Save_Record
  Else
 MsgBox "Serial Number Not Found"
Sheets("Deploy Form").Range("C6:C10").ClearContents
 Sheets("Deploy Form").Range("C12:C15").ClearContents
 End If

End Sub
Sub Save_Record()
Dim lr As Long, lr2 As Long, r As Long
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Set ws1 = Sheets("InStore")
Set ws2 = Sheets("Deploy")
Set ws3 = Sheets("Deploy Form")
lr = ws1.Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 2 Step -1
lr2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row + 1
If ws1.Range("A" & r).Value = ws3.Range("C6") Then
ws1.Rows(r).Copy Destination:=ws2.Range("A" & lr2)
ws1.Cells(r, "A").EntireRow.Delete
End If
Next r
End Sub

I have 3 Worksheets :

Sheet("InStore") is a Table containing Records from Column A:N, while Column M:X contains Formulas referencing to another table.
Sheet("Deploy Form") this is a Form where I have I input a couple of inputs and also search for a Value in "C6" against my Sheet("InStore") table.
Sheet("Deploy") is a table containing Records that I would need to move from "InStore" table after a condition is met when i Save on my DeployForm.

My Save_Record feature I have currently this code, whereby when it meets a Condition in my Deploy Form, it will execute Save_Record() upon the Click of the Save button.

What it actually does is copy the Row found in InStore Sheet where it matches a value found in Sheets("Deploy Form").Range("C6") onto the next available row in Deploy Sheet and proceed to delete the source row.

How can i amend my code to just copy the Row Data found in Sheet("InStore") from Column A to Column N instead of from all the columns. Since I have some vlookup formulas from Column M onwards referencing Table for some values.

I want my code to achieve 2 things, copy the data from InStore and along with some data found in DeployForm(Fixed A1:G1) over to my Deploy Table record.

 

[Yongle]

How can i amend my code to just copy the Row Data found in Sheet("InStore") from Column A to Column N instead of from all the columns

ws1.Rows(r).Copy Destination:=ws2.Range("A" & lr2)

ws1.Rows(r).Resize(, 14).Copy Destination:=ws2.Range("A" & lr2)

 

[Yongle]

I want my code to achieve 2 things, copy the data from InStore and along with some data found in DeployForm(Fixed A1:G1) over to my Deploy Table record.

see post#2 for first bit

You did not specify the PasteTo range (in Deploy Table )
The code below copies the data to columns O:U ,replace O with the first column of your PasteTo range

ws1.Range("A1:G1").Copy ws2.Range("O" & lr2)

 

[Newbie123456]

see post#2 for first bit

You did not specify the PasteTo range (in Deploy Table )
The code below copies the data to columns O:U ,replace O with the first column of your PasteTo range

ws1.Range("A1:G1").Copy ws2.Range("O" & lr2)

ws1.Range("A1:G1").Copy ws2.Range("O" & lr2)

For this command, currently in my A1:G1 columns, some of my cells have formulas inside. How do i just paste the values inside using vba.

 

[Yongle]

Try this instead

ws2.Range("O" & lr2).Resize(, 7).Value = ws1.Range("A1:G1").Value

 

[Yongle]

Another way if cell formatting and number formatting etc is required

ws1.Range("A1:G1").Copy
With ws2.Range("O" & lr2)
    .PasteSpecial (xlPasteAll)
    .PasteSpecial (xlPasteValues)
End With

 

[Fluff]

Cross posted VBA Code to check Cell Value in another sheet, before copy match record to Sheet

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.