Autofill
 

Hello All,
I am trying to find out how to make autofill work in my workbook like this:

New sheet loads into workbook
Range K16 to K45 then needs to be autofilled with numbers.
The numbers will be diffrent for each new sheet that loads, but will carry
on from the sheet before.

ie: sheet1 will contain 1-30, sheet 2 will be 31-60, sheet3 will be 61-90
etc etc

Any idea's welcomed.
Regards
Peter

Autofill
 

Assumes when you "Load" a sheet it will be placed directly after the last
sheet you "Loaded" (in the tab order)

Sub AA()
Dim sh As Worksheet
Dim i As Long
On Error Resume Next
Set sh = ActiveSheet.Previous
On Error GoTo 0
If Not sh Is Nothing Then
i = sh.Range("K45") + 1
Else
i = 1
End If
With ActiveSheet
.Range("K16").Value = i
.Range("K17").Value = i + 1
.Range("K16:K17").AutoFill _
Destination:=.Range("K16:K45"), _
Type:=xlFillDefault
End With
End Sub


--
Regards,
Tom Ogilvy




"Peter" wrote in message
...
Hello All,
I am trying to find out how to make autofill work in my workbook like
this:

New sheet loads into workbook
Range K16 to K45 then needs to be autofilled with numbers.
The numbers will be diffrent for each new sheet that loads, but will carry
on from the sheet before.

ie: sheet1 will contain 1-30, sheet 2 will be 31-60, sheet3 will be 61-90
etc etc

Any idea's welcomed.
Regards
Peter

Autofill
 

Thanks Tom
Worked exactly as I had hoped.

Regards
Peter

"Tom Ogilvy" wrote:

Assumes when you "Load" a sheet it will be placed directly after the last
sheet you "Loaded" (in the tab order)

Sub AA()
Dim sh As Worksheet
Dim i As Long
On Error Resume Next
Set sh = ActiveSheet.Previous
On Error GoTo 0
If Not sh Is Nothing Then
i = sh.Range("K45") + 1
Else
i = 1
End If
With ActiveSheet
.Range("K16").Value = i
.Range("K17").Value = i + 1
.Range("K16:K17").AutoFill _
Destination:=.Range("K16:K45"), _
Type:=xlFillDefault
End With
End Sub


--
Regards,
Tom Ogilvy




"Peter" wrote in message
...
Hello All,
I am trying to find out how to make autofill work in my workbook like
this:

New sheet loads into workbook
Range K16 to K45 then needs to be autofilled with numbers.
The numbers will be diffrent for each new sheet that loads, but will carry
on from the sheet before.

ie: sheet1 will contain 1-30, sheet 2 will be 31-60, sheet3 will be 61-90
etc etc

Any idea's welcomed.
Regards
Peter