Copy cells in a defined range > 0, paste in a defined cell on other worksheet

Hi,

You could for instance use inputbox with type = 8 to ask user where input and output ranges are.
And as you will work with ranges, then looping by range collection members will be probably a natural choice.

See such code (I assumed that you need to copy only the cells with positive values - as described in the post, not whole rows, as it is done in your code);

Sub trial()
Dim inpRng As Range, outCell As Range, currCell As Range

Set inpRng = Application.InputBox(prompt:="Please select input Range", Type:=8)
Sheets("Sheet2").Activate
Set outCell = Application.InputBox(prompt:="Please select output cell", Type:=8).Cells(1, 1)
For Each currCell In inpRng
  If currCell.Value > 0 Then
    currCell.Copy outCell
    Set outCell = outCell.Offset(1, 0)
  End If
Next currCell
End Sub

try below code

Sub trial()
Dim r As Range, myRange As Range, i As Long, sht2 As Worksheet

Set sht2 = Worksheets("Sheet2")
Set myRange = Sheet1.Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
Sheet1.Select

For Each r In myRange

    If r.Value > 0 Then
        i = i + 1
        r.Copy sht2.Range("A" & i + 1)
    End If
Next r
    MsgBox "Ok"
End Sub

Glad to hear so,

So I overestimated the need for flexibility in : "flexibility of choosing a defined range to copy from"
And indeed added not needed flexibility to what you wanted to have defined: "a defined cell to paste the values to"

Just one side note:

If you run code once, and there were 5 positive values in A1:A15 they will be copied to E5:E9
but then when you change your data in A1:A15 and only 2 values are positive, then they will be copied to E5 and E6, but content of E7:E9 will remain unchanged.
So I'd suggest adding clearing contents of possible output cells. for instance in such way:

Sub trial()
Dim inpRng As Range, outCell As Range, currCell As Range, lastrow as long
Set inpRng = Worksheets("Sheet1").Range("A1:A15")
With Worksheets("Sheet2")
  Set outCell = .Cells(5, 5)
  lastrow = .Cells(rows.count, 5).end(xlup).row
  if lastrow<5 then lastrow=5
  .range(.Cells(5, 5),.cells(lastrow,5)).clearcontents
end with

For Each currCell In inpRng
'...

Could be also easier just by adding to code from previous post (anywhere above the For loop).

Worksheets("Sheet2").RAnge("E5:E104").Clearcontents