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

[countryfan_nt]

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

 

[jim may]

Here is a sample of your scenario -- Check it out,,

Excel 2010
	A	B
1
2		144
3		231
4		201
5		411
6		143
7		388
8		394
9		454
10		247
11
Sheet1

Excel 2010
	A	B	C	D	E
1
2		144	415	220	251
3		231	444	272	272
4		201	192	295	332
5		411	441	230	387
6		143	149	365	267
7		333	329	258	418
8		394	330	286	361
9		454	383	344	325
10		247	310	214	108
Sheet2

Run this code:

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

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]

Hi and thank you! Please help me again.
I am facing a problem: Run Time Error 424: Object Required.
After Debugging the following was highlighted:

.Range(.Cells(FR, 2), .Cells(LR, 5)).Copy

thanks a lot!

 

[jim may]

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

 

[countryfan_nt]

ADVERTISEMENT

OK should I assume that sheet1 is like sheet the sheet input? and sheet2 is like DB?

 

[jim may]

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]

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!

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