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?

Tim Williams · Accepted Answer · 2017-07-24 17:45:27Z

3

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

answered Jul 24, 2017 at 17:45

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

curious · Accepted Answer · 2017-07-26 02:10:05Z

0

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.

answered Jul 26, 2017 at 2:10

curious

1,4925 gold badges18 silver badges33 bronze badges

Collectives™ on Stack Overflow

Excel - For Loop

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related