Copy specific cells

[lunatu]

Hi! Maybe a silly and simple question but didn't find an answer...
I want to copy from Sheet 1 rows from columns A-B and E-I into Sheet 2 columns C-J. I was using this code to select entire row but like to change now...:

Sub CopyCell()
Dim LR As Long, i As Long
With Sheets("Sheet 1")
LR = .Range("H" & Rows.Count).End(xlUp).Row
For i = 1 To LR
If .Range("H" & i).Value = "Sales Person 1" And .Range("J" & i).Value = "Sold" Then .Rows(i).copy Destination:=Sheets("Sheet 2").Range("A" & Rows.Count).End(xlUp).Offset(1)
Next i
End With
End Sub

Thanks in advance!

 

[GWteB]

How would you copy 7 columns across to where there are 8 target columns?

 

[lunatu]

Sorry typo... meant columns C-I in Sheet2

 

[GWteB]

I see, try this ...

Sub CopyCell()
    Dim LR As Long, i As Long
    With Sheets("Sheet 1")
        LR = .Range("H" & .Rows.Count).End(xlUp).Row
        For i = 1 To LR
            If .Range("H" & i).Value = "Sales Person 1" And .Range("J" & i).Value = "Sold" Then
                .Range("A" & i).Resize(1, 2).Copy Sheets("Sheet 2").Range("C" & Rows.Count).End(xlUp).Offset(1)
                .Range("E" & i).Resize(1, 5).Copy Sheets("Sheet 2").Range("E" & Rows.Count).End(xlUp).Offset(1)
            End If
        Next i
    End With
End Sub

 

[lunatu]

Works perfectly - thanks a lot!

 

[GWteB]

You are welcome and thanks for letting me know

 

[lunatu]

Hi,
getting back to this... other columns (not C-I) in sheet2 includes some formulas and data validation but all them are disappeared after running this macro. Any idea how to fix this?

 

[GWteB]

It's very unlikely that my macro is responsible for that since it doesn't copy entire rows but just a few cells as per your request in post #1:

I want to copy from Sheet 1 rows from columns A-B and E-I into Sheet 2 columns C-J. I was using this code to select entire row but like to change now...:

When I ignore both the "Sales Person 1" & "Sold" comparison conditions, the result of the macro is per images below, so there's something else going on:

Before:

After:

 

[lunatu]

Hi, yes you're right - it is not because of that macro...
Based on value changing in sheet1 I have macro running automatically two macros, the one above and removing duplicates with this macro:

Sub RemoveDuplicates()

Dim rng As Range

Set rng = ThisWorkbook.Sheets("Sales Person 1").UsedRange

rng.RemoveDuplicates Columns:=5, Header:=xlYes

End Sub

This deletes the whole row, inc. data validation and formulas in other cells. Need to figure out how to remove duplicates but not change other cells

 

[Fluff]

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

As you have started a new thread for this question, I'm closing this one.