How to copy Row from one sheet to another?

Tanquen

Board Regular
Joined
Dec 14, 2017
Messages
78
Office Version
  1. 365
Platform
  1. Windows
I've used this code to find a cell with a value on one sheet and copy a cell adjacent to the found one back to the original sheet, also with an offset.
Now I just want to find the cell and copy the entire row it was found on from one sheet back to the original. Best I can get is a row where each cell is "TRUE" or just the copying the one found cell back to the one source cell.

I guess I could make an offset for every cell in the row but that seems like overkill.

VBA Code:
Sub Button1_Click()
    '***Get Row from the AControl RoomDB Import Firs Sheet
        Dim sh As Worksheet, rng As Range, d As String, fRng As Range, CL As Range
        
        Set sh = Worksheets("New PTCU1 InTouch Import")
            
        With sh
            Set fRng = Worksheets("New PTCU1 InTouch Import").Range("A10:A10")
            
            For Each CL In fRng
                If Not IsEmpty(CL.Value) Then
                    With Worksheets("Control RoomDB Import First").Range("A:A")
                        Set rngFound = .Find(CL.Value, LookIn:=xlValues, LookAt:=xlWhole)
                        If Not rngFound Is Nothing Then
                            'CL.Offset(, 3).Value = Replace(rngFound.Offset(, 1), "_", "-")
                            CL = rngFound.EntireRow.Copy
                        End If
                    End With
                End If
                
            Next CL
        End With
End Sub
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Try to replace:
PHP:
 CL = rngFound.EntireRow.Copy
with
PHP:
rngFound.EntireRow.Copy CL
 
Upvote 0
That worked, thanks.
I was so close, I was just wanting to make "CL = rngFound" like I had before when just copying one cell.

I did have to remove a column I had just added so I did not get the good old error about the rage not matching. Excel is so odd sometimes. So you copy a row but try and paste it into a cell that is not in the 'A' column, why can't just trim off column 'XFD' that is not being used or only copy the used cells in the row in the first place?

Is there an easy way to copy all the cells to the right of the cell found or all the used cells to the right of the vound cell?
 
Upvote 0

Forum statistics

Threads
1,223,250
Messages
6,171,036
Members
452,374
Latest member
keccles

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