Combine similar subs into one sub.

[dfenton21]

Hi,

I have a spreadsheet that is used to book people on training courses. I have a sub that runs an input box for the person’s name, and then adds that name to the first blank cell of a specified range.

However, I have about 20 training sessions, with one sheet per session. I want to avoid having 20 different subs. It is possible to specify that I want the sub to work with the specified range in the current worksheet.

To further complicate the situation, the specified range for the names could be different sizes for each worksheet, as the maximum number differs for each session. I could add a hidden column to identity the rows that can be used if needed.

Have you any ideas how to use one sub module for all worksheets.

Thanks.

 

[RoryA]

The sub goes into a normal module and you can use activesheet to specify the current sheet. Alternatively, change your processing sub so that it accepts a range object as a parameter, then call it from each sheet, telling it which range to look at.

 

[ColinKJ]

Hi dfenton21,

You could add something like the following, assuming that you call the inputbox when the required sheet is active, and the names go into column A.

r = ActiveSheet.Range("A65536").End(xlUp).Row + 1
Cells(r, 1) = InputBox("Enter Name")

ColinKJ

 

[dfenton21]

That's perfect, thanks.

As I said, I have a macro that adds the response from the input box to the first blank cell of a range (B2:B20). Is it possible to modify this to only include cells with x in the corresponding cell in column A, e.g. if x does not appear in each cell between A15:A20, it will add the response to B14 even if B15:B20 is blank. Hope this makes sense.

Thanks again.

 

[ColinKJ]

dfenton21,

I think this is what you want:

r = ActiveSheet.Range("B20").End(xlUp).Row + 1
If r > 14 And Cells(r, 1) <> "x" Then r = 14
Cells(r, 2) = InputBox("Enter Name")

ColinKJ