Pasting into a named range and expanding the range

[rob1987]

Hi,

I have some data I want to copy from a range named "Benefitsinput" in worksheet "Sheet1" into a range named "Benefits" in worksheet "Templates". However, when I run my macro and paste this data into the last row in the "Benefits" range I want the range to expand to include this new row.

The Macro I have currently is:

Sub Copy_Range()
With Range("Benefits")
.Resize(.Rows.Count + 1, .Columns.Count).Name = "Benefits"
End With
Worksheets("Sheet1").Range("Benefitsinput").Cut Destination:=Range("Benefits").Cells(1, 1).Offset(Range("Benefits").Rows.Count, 0)
End Sub

This works for the first time I run my macro but the next time I try to run it with a new set of data in "Benefitsinput" I get a runtime error '1004': Application defined or object-defined error.

Please can anyone point out where I am going wrong?

Thank you

 

[Andrew Poulsom]

You are cutting Benefitsinput, so it is now on sheet Templates rather than Sheet1. Try copying then clearing the contents.

 

[rob1987]

As simple as that.

Thank You.

 

[SydneyGeek]

The problem is mainly with the cut and paste. When you cut a range and paste elsewhere, you transfer the range reference to the new destination. So, next time you look for range("BenefitsInput") it is no longer on Sheet1.
Try...

Sub AnotherCopy()
    Range("BenefitsInput").Copy _
        Destination:=Range("Benefits").End(xlDown).Offset(1, 0)
    Range("BenefitsInput").ClearContents
End Sub

{EDIT} Got sidetracked; Andrew beat me to the punch.

Denis