# C&P if condition met, or just paste in new row.

#### countryfan_nt

##### Well-known Member
Hello friends, hope all is well.

I have:
• 2 identical sheets: “DB”. & “Input” .
• The ranges are B43:R64.

I need a macro that:
A. Will go through the sheet “Input”.
B. Looks at column B43:B64 if the numbers in each of these cells match the cells of sheet “DB” (column B43:B64) THEN.

The rows is of “Input” is copied and replaces the contents of the row B43:R43 (and so on).
If it NOT, then the row is copied onto a new empty range (row).

I really appreciate your time and efforts!

All the best,
Countryfan_nt

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### jim may

##### Well-known Member
Here is a sample of your scenario -- Check it out,,

Excel 2010
AB
1
2144
3231
4201
5411
6143
7388
8394
9454
10247
11
Sheet1

Excel 2010
ABCDE
1
2144415220251
3231444272272
4201192295332
5411441230387
6143149365267
7333329258418
8394330286361
9454383344325
10247310214108
Sheet2

Run this code:

Then Change Cell B7 in Sheet2 to agree with Cell B7 in Sheet1

Code:
``````Sub Foo()
Dim Pass As Boolean
Dim FR As Long, LR As Long
FR = Range("B1").End(xlDown).Row
LR = Range("B" & Rows.Count).End(xlUp).Row
With Sheet2
For i = FR To LR - FR + 1
If Cells(i, 2) = .Cells(i, 2) Then
Pass = True
Else
Pass = False
Exit For
End If
Next i
.Range(.Cells(FR, 2), .Cells(LR, 5)).Copy
End With
With Sheet1
If Pass Then
.Cells(FR, 2).PasteSpecial xlPasteValues
Else
.Cells(20, 2).PasteSpecial xlPasteValues
End If
End With
End Sub``````

Write back if questions... Jim

#### countryfan_nt

##### Well-known Member
I am facing a problem: Run Time Error 424: Object Required.
After Debugging the following was highlighted:

Code:
``.Range(.Cells(FR, 2), .Cells(LR, 5)).Copy``

thanks a lot!

#### jim may

##### Well-known Member
You are in a New Workbook with The above provided data in the respective Sheets named: Sheet1 and Sheet2 ?

#### countryfan_nt

##### Well-known Member
OK should I assume that sheet1 is like sheet the sheet input? and sheet2 is like DB?

#### jim may

##### Well-known Member
Yes, What I presented is a completely separate/different from your specifics. It is meant as a "learning-tool" for you to apply to your actual File. HTH, Jim

#### countryfan_nt

##### Well-known Member
OK this was what I was able to make so far. I am still having trouble. Please help. there is an error.

I am trying to separate the if statements and the THEN reaction to the If statements.

example: if X, then A. If Y then B. and each if has loops.

thanks a whole lot!

Code:
``````Sub UpldChgInp()

Dim i As Long, PasteToRow As Integer

Sheet9.Visible = xlSheetVisible
Sheets("DB").Select

If Sheets("Input").Range("e34").Value <> "CORRECT PASSWORD" Then
Exit Sub
End If

' Application.ScreenUpdating = False

PasteToRow = 43
For i = 43 To Sheets("DB").Cells.SpecialCells(xlLastCell).Row

If Sheets("Input").Range("B" & i).Value = Sheets("DB").Range("D" & i).Value Then

Sheets("Input").Range("B" & i & ":R" & i).Copy
Sheets("DB").Range("B" & i & ":R" & i).Paste

Next i

If Sheets("Input").Range("B" & i).Value <> Sheets("DB").Range("B" & i).Value Then

Sheets("Input").Range("B" & i & ":R" & i).Copy

Sheets("DB").Select

Range("B43").End(xlDown).Offset(1, 0).Select

Sheets("DB").Range("B" & i & ":R" & i).Paste

End If
Next i

Sheet9.Visible = xlSheetVeryHidden

Sheets("Input").Select
Range("A37").Select
End Sub``````

Replies
2
Views
140
Replies
7
Views
477
Replies
1
Views
93
Replies
2
Views
466
Replies
3
Views
160

1,195,692
Messages
6,011,168
Members
441,591
Latest member
elmogm

### 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.

### Which adblocker are you using?

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

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