0

I'm needing help on how to modify the code below to instead copy to the first available in another worksheet. So far the code copy from sheet 1 to sheet2 but starting in row 1 by default

Thanks in advance

Sub Copy15()

Dim c As Range

Dim j As Integer

Dim Source As Worksheet

Dim Target As Worksheet


Set Source = ActiveWorkbook.Worksheets("Sheet1")
Set Target = ActiveWorkbook.Worksheets("Sheet2")

J = 1
For Each c In Source.Range("E1:E1000")
    If c = "yes" Then
       Source.Rows(c.Row).Copy Target.Rows(j)
       j = j + 1
    End If
Next c

End Sub

Wendi
  • 35
  • 6
  • `j=target.range("a" & target.rows.count).end(xlup).row` Target isnt a very good name for sheet as can be used in worksheet change events. – Nathan_Sav Jan 25 '21 at 10:15
  • `j = Target.Range("E" & Target.Rows.Count).End(xlUp).Row` In this example, since we can't know what other cells are populated. Might not be great either, depending on what sheet 2 looks like. – Christofer Weber Jan 25 '21 at 10:21
  • 1
    Use [Autofilter](https://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s). It is much faster :) – Siddharth Rout Jan 25 '21 at 10:25

1 Answers1

0

Use the next code, please:

Sub Copy15()
Dim Source As Worksheet, Target As Worksheet, rng As Range
Dim c As Range, lastRowS As Long, lastRowT As Long, rngU As Range

Set Source = ActiveWorkbook.Worksheets("Sheet1")
Set Target = ActiveWorkbook.Worksheets("Sheet2")
lastRowS = Source.Range("E" & Source.rows.count).End(xlUp).row 'last row
lastRowT = Target.Range("E" & Target.rows.count).End(xlUp).row + 1 'last empty row

Set rng = Source.Range("E2:E" & lastRowS)
For Each c In rng
    If c = Source.range("Y2").value  Then
       If rngU Is Nothing Then
           Set rngU = c 'set the first cell of the range to be copied
       Else
           Set rngU = Union(rngU, c) 'make a Union for(yes") ranges
       End If
    End If
Next c
'copy everything at once:
If Not rngU Is Nothing Then _
    rngU.EntireRow.Copy Target.Range("A" & lastRowT)
End Sub

But do you really need to copy all the rows?

FaneDuru
  • 38,298
  • 4
  • 19
  • 27
  • This code works very well. Also is it possible to replace criteria "yes" with range say Source.range("Y2").value ? – Wendi Jan 25 '21 at 10:46
  • @Wendi Yes, of course, it is... you should replace "yes" with what you suggested. I will adapt the above code to do it. – FaneDuru Jan 25 '21 at 10:50
  • @Wendi But i did it 21 minutes ago... Please, refresh the page (this one) and take the updated code. Besides that, in order to learn, you must dare more! Nothing wrong could happen... – FaneDuru Jan 25 '21 at 11:12