Problem with Autofill Macro

B

Ben Allen

I have a macro which copies down formula so they are always 100 more than
the current no. of entries. This code is activated when the worksheet is
activated. However i get an error with the line " Selection.AutoFill
Destination:=Range(Left & ":" & RightFill), Type:=xlFillDefault " My code is
below can anyone help? Thank you.

Private Sub Worksheet_Activate()
'Define Varaibles
Dim nr As Integer
Dim Left As String
Dim Right As String
Dim RightFill As String
Dim RowSelect As String
Dim RowFill As String
'Set Varaibles
nr = Application.CountA(Range("B:B")) + 2
Left = "K" & nr
Right = "N" & nr
RightFill = "K" & nr + 100
RowSelect = Left & ":" & Right
RowFill = Left & ":" & RightFill
'Autofill
Range(RowSelect).Select
Selection.AutoFill Destination:=Range(Left & ":" & RightFill),
Type:=xlFillDefault
Range("B4").Select
End Sub
--
Cheers,
Ben

Remove your.tonsils to reply
"You only live once but if you do it right once is enough!"
 
T

Tom Ogilvy

You need to be consistent between right and rightfill. Either both should
be in column N or both in Column K. This has both in column N

Private Sub Worksheet_Activate()
'Define Varaibles
Dim nr As Integer
Dim Left As String
Dim Right As String
Dim RightFill As String
Dim RowSelect As String
Dim RowFill As String
'Set Varaibles
nr = Application.CountA(Range("B:B")) + 2
Left = "K" & nr
Right = "N" & nr
Debug.Print Right
RightFill = "N" & nr + 100
RowSelect = Left & ":" & Right
RowFill = Left & ":" & RightFill
'Autofill
Range(RowSelect).Select
Selection.AutoFill Destination:=Range(Left & ":" & RightFill),
Type:=xlFillDefault
Range("B4").Select
End Sub
 
B

Bob Phillips

Ben,

RightFill is incorrectly defined. It should poin t to N

RightFill = "N" & nr + 100

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top