Need help: Run-time error '1004' in VBA code

[mmann1973]

I have been running this code successfully, for a while now. For some reason it just started throwing this error code:
Run-time error '1004':
Method 'Range' of object'_Global' failed


Sub New_Associate()
Dim NextRow As Range
Set NextRow = Range("A1" & Sheets("Helper").UsedRange.Rows.Count + 1)
Sheets("New Associate Entry").Range("C2:C7").Copy
Sheets("Helper").Activate
NextRow.PasteSpecial Paste:=xlValues, Transpose:=True
Application.CutCopyMode = False
Set NextRow = Nothing

End Sub


I'm not sure what's causing the error, any help in solving would be appreciated. Only thing I see that makes me wonder is, the page the data is stored on, just reached line 100.

I am running Excel 2016

 

[JoeMo]

You didn't tell us what line is highlighted when the error occurs so I'll assume it's this one:

Set NextRow = Range("A1" & Sheets("Helper").UsedRange.Rows.Count + 1)

which looks a bit strange. In effect, if you have say 100 rows in the used range of "Helper" then this sets NextRow equal to the cell: A1101
Is that really what you want? Maybe:

Set NextRow = Range("A" & Sheets("Helper").UsedRange.Rows.Count + 1)

 

[JLGWhiz]

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

This cannot execute because the value of the 'NextRow' variable is invalid.

Change this

Set NextRow = Range("A1" & Sheets("Helper").UsedRange.Rows.Count + 1)

To This

Set NextRow = Range("A" & Sheets("Helper").UsedRange.Rows.Count + 1)

 

[mmann1973]

Sorry for not saying which row was throwing the error, you were correct in your choosing.

Your solution fixed my problem...Thank you!

 

[JoeMo]

You are welcome - thanks for the reply.