Home |
Search |
Today's Posts |
#1
|
|||
|
|||
adapt macro
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 |
#2
|
|||
|
|||
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).Offs et(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" wrote in message ... 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 |
#3
|
|||
|
|||
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 = _ 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" wrote in message ... 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 . |
#4
|
|||
|
|||
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) "Monty" wrote in message ... 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 = _ 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" wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Playing a macro from another workbook | Excel Discussion (Misc queries) | |||
Date macro | Excel Discussion (Misc queries) | |||
Can't get simple macro to run | Excel Worksheet Functions | |||
Macro and If Statement | Excel Discussion (Misc queries) | |||
Macro for multiple charts | Excel Worksheet Functions |