Problem with Autofill Macro
 

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!"

Problem with Autofill Macro
 

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

--
Regards,
Tom Ogilvy

"Ben Allen" wrote in message
...
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!"

Problem with Autofill Macro
 

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)

"Ben Allen" wrote in message
...
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!"