How do I automate updating VBA modules

[krpurcel]

I have a spreadsheet that has multiple macros. They are stored in multiple Modules. Each macro contains a reference to a week number, i.e. Week 5. I want to automate this so I can enter the week number as a variable somewhere and have the different macros updated with the new week number.

I don't know how to do that. In the past I have recorded macros and then edited them. But doing that does not work, as the new recorded macro stops as soon as you select "edit" for the existing macro.

Would someone please point me in the right direction on how to accomplish this?

Thanks,
Kevin

[davsth]

Decide the week number, either by entering it in a dialog box or just putting it in a cell and referencing the cell. Then declare its scope

http://www.excel-easy.com/vba/exampl...ble-scope.html

[krpurcel]

Thanks for the reply davsth.

I have declared a variable in the declaration section to be used in all the modules - Public wk As String

I have created a Range on one of the worksheets named "week"

In my module I put: wk = Range("week").Select

When I run it I get an error saying "Unable to get the Select property of the Range class".

I tried it without the quote marks - Range(week).Select. It liked that even less.

I tried identifying the specific worksheet - wk = ActiveWorkbook.Worksheets("AgainstTheSpread").Range("week").Select. Got the same error message that it is unable to get the Select property.

Any clue as to what I am doing wrong?

Thanks,
Kevin

[Kyle123]

Don't select:

Please Login or Register  to view this content.

[krpurcel]

Thanks Kyle! That did it!