AutoFill To Unknown Column

[Dale]

Background.

From a varying Range I would like to AutoFill across to a varying
column picked by the user.

Range differs on different sheets. Might be D1629 or C14:C321 or
H1:H4, etc.

The user picks the column from a UserForm which returns a number to a
named range on sheet1.

I was hoping to be able to use this number to increament the column
number but I get either
"Error 1004, autofill method of range class failed" or
"Error 91, Object variable or With block variable not set"

Suggestions ?

 

[Dave Peterson]

I didn't bother to make a userform (I just used application.inputboxes). But
this might give you some ideas. The first 2/3's of this is to just get the
range and column. The last little bit does the work. And I used xlfillcopy.
Not sure if you wanted that.


Option Explicit
Sub testme01()

Dim myRngToCopy As Range
Dim myColToStopFill As Long
Dim wks As Worksheet
Dim destRng As Range

Set wks = ActiveSheet
Set myRngToCopy = Nothing
On Error Resume Next
Set myRngToCopy = Application.InputBox(prompt:="Select a Range to fill", _
Title:="First column will be used", _
Default:=Selection.Address, Type:=8)
On Error GoTo 0

If myRngToCopy Is Nothing Then
Exit Sub
End If

Set myRngToCopy = myRngToCopy.Areas(1).Columns(1)

myColToStopFill = 0
On Error Resume Next
myColToStopFill = Application.InputBox _
(prompt:="Select a column to stop filling", _
Title:="First cell will determine column", _
Type:=8).Cells(1, 1).Column
On Error GoTo 0

If myColToStopFill = 0 Then
Exit Sub
End If

With myRngToCopy
If .Column = myColToStopFill Then
Exit Sub 'we're done
Else
If .Column < myColToStopFill Then
Set destRng = .Resize(, myColToStopFill - .Column + 1)
Else
Set destRng = .Cells(1).Offset(0, myColToStopFill - .Column) _
.Resize(.Rows.Count, _
.Column - myColToStopFill + 1)
End If
.AutoFill _
Destination:=destRng, _
Type:=xlFillCopy
End If
End With

End Sub