Excel - For Loop

Question

Sub Macro11()
'
' Macro11 Macro
'

'
Calculate
Range("B46:CRZ46").Select
Selection.Copy
Sheets("Sheet1").Select
Range("B:B").Select
Cells(Range("B:B").End(xlUp).Row + 1).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
End Sub

What the following Macro does, is refresh the page (which creates a new data set), copy the data set from ("B46:CRZ46") and paste it in "Sheet1". Now, I want the pasting to go down a row every time (which I tried with .End(xlUp) however I am not using it correctly and it just copies and pastes on the same row.

What could I change or add to for the code to function well?

Answer 1

Simply replace the line:

Cells(Range("B:B").End(xlUp).Row + 1).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False

with:

Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlValues

Explanation:
Range("B:B").End(xlUp) will take you to the top. Instead, the approach here should be to go to the bottom by Range("B" & Rows.Count) and then End(xlUp).Offset(1, 0) to go to 1 row below the 1st non-blank cell from bottom.

Answer 2

You can assign values directly without needing to copy/paste:

Dim rng As Range

Calculate

Set rng = Activesheet.Range("B46:CRZ46")

Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp) _
       .Offset(1, 0).Resize(rng.rows.count, rng.columns.count).Value = rng.Value