Error when selecting range of variable size.

[Kirby1996]

Hi-

I haven't had much need to write VBA code in a few years, so I'm pretty sure this is straightforward.

I'm trying to copy paste a range of variable size and am getting a '1004' error when selecting to the right.

Basically, I have input data, and then output data which applies formulas to the input data. The size of the input data can vary, so I wish to paste some formulas to a range of the same length as the input data. The macro is initiated by an ActiveX button on another worksheet.

My code is as follows:

Private Sub CommandButton1_Click()


'Count rows in H&A file


Dim last_row As Long


last_row = Worksheets("H&A Input").Cells(Rows.Count, 1).End(xlUp).Row


MsgBox (last_row)

'Paste formulas to range the same length as H&A Input

Worksheets("H&A Output").Activate
Worksheets("H&A Output").Range("A4").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.AutoFill Destination:=Range("A5:A" & CStr(last_row)), Type:=xlFillDefault


End Sub

As mentioned, the error is occurring on the Range(Selection... line.

Any help?

Much appreciated!

Regards...

 

[RoryA]

Welcome to the forum.

It looks like your code is probably in a worksheet code module, so Range without a worksheet qualifier, refers to that sheet, not the active sheet. Also, the autofill destination has to start on the same row as you are filling from, and must include all the columns of the source range. So, for example:

With Worksheets("H&A Output")
With .Range("A4", .Range("A4").End(xlToRight))
    .AutoFill Destination:=.Resize(last_row - 3), Type:=xlFillDefault
end with
end with

 

[Kirby1996]

Welcome to the forum.

It looks like your code is probably in a worksheet code module, so Range without a worksheet qualifier, refers to that sheet, not the active sheet. Also, the autofill destination has to start on the same row as you are filling from, and must include all the columns of the source range. So, for example:

With Worksheets("H&A Output")
With .Range("A4", .Range("A4").End(xlToRight))
    .AutoFill Destination:=.Resize(last_row - 3), Type:=xlFillDefault
end with
end with

Ahh. Thanks. Not just the fix, but the background on the scope of the code within the worksheet. That's the key I needed.

Appreciate it!