Copying specific data to another sheet based on cell value

[JJabra]

Hi guys,

What I am looking to do is copy data from one sheet to another, however I only want it to copy columns A and B based on the cell value of Column E in each row

So far I have the following code which is able to copy the data, but currently pastes it on every column on the worksheet and I can't see why. Apologies if it is something simple, I am relatively new to VBA.

Sub HoldCodeSeparation()


Dim Source As Worksheet
Dim Target As Worksheet




Set Source = ActiveWorkbook.Worksheets("Hold Codes")
Set Target = ActiveWorkbook.Worksheets("Extract")


j = 2 ' Start copying to row 1 in target sheet
For Each c In Source.Range("E1:E1000") ' Do 1000 rows
If CStr(c) = "22" Then
Source.Range(Cells(c.Row, 1), Cells(c.Row, 2)).Copy Target.Rows(j)
j = j + 1
End If
Next c


End Sub

 

[Fluff]

Which column do you want to copy the data to?

 

[JJabra]

Apologies,

I want the data to copy to columns A and B on the second sheet

 

[Fluff]

In that case use

Source.Range(Cells(c.Row, 1), Cells(c.Row, 2)).Copy Target.[COLOR=#ff0000]Cells(j,1)[/COLOR]

 

[JJabra]

Thanks! that works, one last thing, is it possible to match destination formatting? not a massive issue, however would be useful if it could

 

[Fluff]

If you only want values use

Target.Cells(j, 1).Resize(, 2).Value = source.Range(Cells(c.Row, 1), Cells(c.Row, 2)).Value

 

[JJabra]

Amazing! thank you very much for your help!

 

[Fluff]

Glad to help & thanks for the feedback

 

[JJabra]

I have been trying to amend the code to now copy data from Column A and F instead of A and B, the data would still be pasted to columns A and B on the target sheet, any suggestions?

 

[Fluff]

How about

Source.Range("A" & c.Row & ",F" & c.Row).Copy
Target.Cells(j, 1).PasteSpecial xlPasteValues