Autofill:already fillup coding

[Peter]

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

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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

[Peter]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>