copy and pasting data to another worksheet with loop and if condition

Question

The following code seems to run smoothly but nothing was copied onto the desired page

Sub a2()


Sheets.Add.Name = "25 degree"
Sheets("25 degree").Move after:=Sheets("data")


Dim x As Long

For x = 2 To 33281

If Cells(x, 1).Value = 25 Then

    Cells("x,1:x,2:x,3:x,4:x,5:x,6").Copy
     Worksheets("25 degree").Select
     ActiveSheet.Paste

End If

Next x

End Sub

`x` is a variable, so you cannot include it inside of your string... and you would want to use `Range` instead of `Cells` in your case... `Range(x & ",1:" & x & ",2")` etc... — braX, Feb 07 '20 at 09:18
Also I highly recommend not to use `.Select` or `ActiveSheet` instead specify the sheet for each `Cells()` object according to [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Feb 07 '20 at 09:22
All the code is strange, I am afraid... Besides the wrong range definition, your code adds a new sheet, which will become your `ActiveSheet`. Then You iterate between the empty newly sheet A:A range and apoteoticaly select it to paste something... In which range would you like to paste the values? — FaneDuru, Feb 07 '20 at 09:36

score 0 · Answer 1 · answered Feb 07 '20 at 09:36

I highly recommend not to use .Select or ActiveSheet instead specify the sheet for each Cells() object according to How to avoid using Select in Excel VBA.

Option Explicit

Public Sub DoSomeCoypExample()
    Dim wsSource As Worksheet
    Set wsSource = ThisWorkbook.ActiveSheet
        'better define by name
        'Set wsSource = ThisWorkbook.Worksheets("source sheet")

    Dim wsDestination As Worksheet
    Set wsDestination = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets("data")) 'add at the correct position and set it to a variable
    wsDestination.Name = "25 degree" 'so you can use the variable to access the new added worksheet.

    Dim iRow As Long
    For iRow = 2 To 33281 'don't use fixed end numbers (see below if you meant to loop until the last used row)
        If wsSource.Cells(iRow, 1).Value = 25 Then
            With wsSource
                .Range(.Cells(iRow, 1), .Cells(iRow, 6)).Copy Destination:=wsDestination.Range("A1")
                'this line will copy columns 1 to 6 of the current row
                'note you need to specify the range where you want to paste
                'if this should be dynamic see below.
            End With
        End If
    Next iRow
End Sub

If you want to loop until the last used row you can get that with something like

Dim LastRow As Long
LastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row 'last used row in column A

If you want to paste into the next free row in your destination worksheet instead of a fixed range Destination:=wsDestination.Range("A1") you can use the same technique as above to finde the next free row:

Dim NextFreeRow As Long
NextFreeRow = wsDestination.Cells(wsDestination.Rows.Count, "A").End(xlUp).Row + 1

So you can use that in your paste destination:
Destination:=wsDestination.Range("A" & NextFreeRow)

copy and pasting data to another worksheet with loop and if condition

1 Answers1