adapt macro

[monty]

If I have the following macro running in worksheet 1.
This will generate a numeric value starting with 1

Option Explicit
Sub RCA()

ActiveSheet.UsedRange
If Range("A1").Value = "" Then
Range("A1").Value = InputBox("Enter starting
value")
Else
Range("A1").SpecialCells
(xlCellTypeLastCell).Offset(1, 0).Value =
Application.WorksheetFunction.Max(Range("A:A")) + 1
End If
End Sub

How can I adapt this macro to run in worksheet 2 & 3. If
I want worksheet 2 to start with PCI1 then PCI2 and so on
and then I want worksheet 3 to start with PCICR1 then
PCICR2.

Thanks

Monty

 

[Bob Phillips]

Try something like

Sub RCA(sh as worksheet)

With sh
.UsedRange
If .Range("A1").Value = "" Then
.Range("A1").Value = InputBox("Enter starting value")
Else
.Range("A1").SpecialCells(xlCellTypeLastCell).Offset(1, 0).Value
= _
Application.WorksheetFunction.Max(.Range("A:A")) + 1
End If
End With
End Sub


and use like so

RCA Activesheet
RCA Worksheets("Sheet2")
RCA Worksheets("Sheet3")

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Monty]

Soz

When i insert this as a module and i go to run as macro
nothing appears. i suppose i'm doing something incorrect
can you please help.

-----Original Message-----
Try something like

Sub RCA(sh as worksheet)

With sh
.UsedRange
If .Range("A1").Value = "" Then
.Range("A1").Value = InputBox("Enter starting value")
Else
.Range("A1").SpecialCells

(xlCellTypeLastCell).Offset(1, 0).Value

 

[Bob Phillips]

A little difficult from afar.

A bit of detail as to what your final code looks like and what happens might
help.

--

HTH

RP
(remove nothere from the email address if mailing direct)