VBA - How to copy different ranges to anew Sheet

Question

I'm trying to copy different ranges if a condition is met. For example, if in column X the word "TFX" is found, then copy the entire row and all rows after if have information (only the first row have the word "TFX").

Here is the code that I have so far. I was trying to copy only 3 rows but I found that in some cases are more than 3.

Sub CopySAbiertopropose()

Dim c As Range
Dim j As Integer
Dim Source As Worksheet
Dim Target As Worksheet
Dim wb As Workbook

Call NewWorkBook

Workbooks("Proposenet General").Sheets(1).Rows(1).Copy
Workbooks("Proposenet IX-OM-VA").Sheets(1).Rows(1).Paste

' Change worksheet designations as needed
Set Source = Workbooks("Proposenet General").Sheets(1)
Set Target = Workbooks("Proposenet IX-OM-VA").Sheets(1)

j = 2     ' Start copying to row 2 in target sheet
For Each c In Source.Range("N1:N50000")   ' Do 50000 rows
    If c = "TFX" Then
       Source.Range("A" & c.Row - 1 & ":V" & c.Row - 1).Copy Target.Range("A" & j) 
       Source.Range("A" & c.Row & ":V" & c.Row).Copy Target.Range("A" & j)
       Source.Range("A" & c.Row + 1 & ":V" & c.Row + 1).Copy Target.Range("A" & j)
       j = j + 1
    End If
Next c

End Sub

If you need some extra info please tell me.

Regards.

My approach would be: 1) Dont loop through each cell. Use .findnext. 2) I would store found rows in an Array and copy paste the whole array at once — JvdV, May 28 '18 at 10:53
hi JvdV thanks for your help. can you tell me how can i store found rows an array? thanks again — Pinto André, May 28 '18 at 11:18
Yes, have a look here: https://stackoverflow.com/questions/50286950/excel-vba-how-to-find-multiple-match-data/50288922#50288922 — JvdV, May 28 '18 at 11:29

VBA - How to copy different ranges to anew Sheet

0 Answers0