Variables in macros?

Is it possible to use variables inside a macro?

I have created different macros:
1. copy and rename worksheets within the workbook.
2. change multiple cells to new dynamic link paths (pulling data from certain cells in a different excel project altogether).

what I am in dire need of is the ability to write the macro so it does this:
- copys current sheet to end of list of sheets.
- renames new sheet.
- applies macro2 (from above) to the new sheet.
- rinse and repeat.

i will have a total of 128 sheets (when done) and i will have to update at least 7 columns with 12 rows in each column (roughly 10,752 changes... way to many to do by hand).

as reference, the path to the resources i am pulling from is as such:
"='c:\Folder\[Jan Report]Manger1'!R8C2" (or B8 on the Manager1 sheet in the Jan Report.xls file)
i am needing the 'Jan Report' and 'Manager1' to be variables. the R8C2 would be the same cell for each month. and it would change to R9C2 for the next sheet in the workbook (the one that macro1 created).

have i totally lost everyone? proabably. just let me know if you need more info and i will try to clear it up. again, i am mainly needing to know if (and how) to get variables inside a macro.

-Nym

Hi,

Yes VBA will accept variables ...
My recommendation is to go step by step ...

Before looping through 128 worksheets,
Turn on the macro recorder
proceed with your usual steps with one worksheet
stop macro recorder
analyse how VBA has translated your actions in code ...

Then will come the time of introducing variables, looping, etc ...

HTH
Carim

Thanks Carim, been there and done that.
I happen to not be just any office nerd, I am a geek at heart and trade (office is just not my stong point), I know how to code in varias languages (VBA is NOT one of them, unfortunately).

Warning Lots and Lots of ugly code:

Please Login or Register  to view this content.

Thats for just one column. i have copies and pasted that 6 more times (making one ugly batch of code) and chaning the R8C2 to R8C3,C4,C5,C6,C7,C9.
The only things that i have to change is the Name of the Manager (David Keys) and the Row/Column reference. the name of the excel doc changes as well (01,02,03,04....12).

Hope this helps a little bit more.
-Nym

Nym,

You are absolutely right ... !!!

That is what happens when code is not tested ... I am bad ...!!!

Carim

Nym,

Code adjusted ...

Please Login or Register  to view this content.

As you have noticed, you are now in a position to simplify all your code even further by building loops with all your strings ...if need be ...

HTH
Carim

Nym,

Do you need absolute references addresses ...?
Had not noticed you also needed the row number as a variable ..

Please Login or Register  to view this content.

HTH
Carim

building loops he says.... lol
I was venturing out and building these said loops when lo and behold, I had no clue what I was doing. I thought I had set it up right, when WHAM! infinate loop.
HOLY CRAP!!!
so, um, just how do i set that loop up so that i dont have to kill the EXCEL process in task manager again. (i did it 3 times then gave up on looping).

Nym,

There are many different ways to create loops ...
Just give me an example of what you trying to accomplish ...
If I can I will give you a hand ...or a hint ....

HTH
Carim

Ok, i will try to explain, as i am sure it is all a mess. :D

as i am visual person, my quasy-excel doc:
http://smilkduds.googlepages.com/SAN...stingMacro.xls
and the remote doc i am pulling data from:
http://smilkduds.googlepages.com/PM-M01-07.xls

The column varabile will change a number of times per work sheet. the row varable will change once per sheet.

In this doc, Column B(and C) pull from Column B from the remote document.
D pulls from C,
E from D,
F from E,
G from F,
H from G,
I is used for a percentage formula,
J from I on the remote doc.

Every sheet will pull from the same columns, only the row reference with change (go up by 1 [8-9-10-11...])

Hope that helps.
ultimatly i want the macro to:
1. Copy the current worksheet to the end of the list of worksheets.
2. Change name of the worksheet to that of a certain property.
3. Change cell A2 to the same name as the worksheet.
4. Select cell B6.
5. Run the macro (or automatically edit) the one I am currently trying to get working.
6. Start over, but give this new worksheet a different name.
7. Continue doing this over and over for all 128 properties that I have.

-Nym

Nym,

Thanks for the links ... it does help to understand your business requirement ...

Attached is a slightly revised template with an amended version of Autofill macro ...

A couple of questions remain unclear for me :
1. With 9 Managers and 128 properties, don't you need an "assignment table" linking them ... a "who-does-what" kind of table ... ?
2. The logic behind the row increment 8, 9, etc ... (which by the way is not yet coded ) does not appear logical ... since template summarizes months whereas Managers sheets deal with properties ...

HTH
Carim

Hi,

Have you already sorted out everything ...?

Carim