Excel VBA - Next Row error on Activated Sheet

Question

I have an Excel 2013 workbook.

I was utilising this SO Answer for Copy and find next blank Row

My code is

Sub Save()
    Dim NextRow As Range
    Set CopyZone = Worksheets("Sheet1").Range("A2:AO289")
    Set ToDataSheet = Worksheets("_data")
    Set NextRow = Range("B" & ToDataSheet.UsedRange.Rows.Count + 1)
    CopyZone.Cut
    ToDataSheet.Activate
    NextRow.PasteSpecial Paste:=xlValues, Transpose:=False
    Application.CutCopyMode = False
    Set NextRow = Nothing
End Sub

Copyzone is the output from my first sheet which has the newly formatted data. I set DataSheet to my to data sheet ("_data"), however it goes into debug on the line.

NextRow.PasteSpecial Paste:=xlValues, Transpose:=False

How do I get it to complete a paste the cut data into the to sheet on the next blank line?

None just stops and highlights that line no error. I have found that it is specific to the cut function as using copy works. — sayth
– sayth, Commented Aug 9, 2016 at 5:45
From your code I infer that the active sheet at the start of your sub isn't "_data", if so when you set NextRow it won't be on "_data" since you don't specify the worksheet. It should be Set NextRow = ToDataSheet.Range("B" & ToDataSheet.UsedRange.Rows.Count + 1) — Vincent G
– Vincent G, Commented Aug 9, 2016 at 6:50

Karthick Gunasekaran · Accepted Answer · 2016-08-09 05:46:49Z

3

Hope the below code solves your problem

Sub Save()
    Set CopyZone = Worksheets("Sheet1").Range("A2:AO289")
    Set ToDataSheet = Worksheets("_data")
    CopyZone.Cut Destination:=ToDataSheet.Range("B" & ToDataSheet.UsedRange.Rows.Count + 1)
End Sub

answered Aug 9, 2016 at 5:46

Karthick Gunasekaran

2,7231 gold badge17 silver badges26 bronze badges

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

2 Comments

sayth Over a year ago

Perfectly simple and sweet.

Karthick Gunasekaran Over a year ago

Thanks sayth for your comments.

Collectives™ on Stack Overflow

Excel VBA - Next Row error on Activated Sheet

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related