Referencing a sheet name

[EMyk08]

Hello community!

Thanks for having an Excel help forum! I am a very new user to Excel. Everything that I know about it, is self taught. I have had a half semester's of C++ training eight years ago, so I can generally read through a little of the vba syntax and understand its semantics. Basically, I get the 'general' concept of what is going on, but I have a rough time programming.

I just discovered the dynamics of this 'macro'. And I am having so much fun with it! I'm rather shocked that I haven't played with and figured Excel out sooner!

Here is my issue. I have a macro that I recorded and I can only use it when the sheet I am viewing is called "Sheet1". If I was to run the macro anytime after that, Excel has a brain fart. I believe this line of code is the thorn in my side:

Sheets("Sheet1").Select

Is there a way that I could change the code and reference the current sheet the macro is running on? Possibly something like this:

Sheets(The_Current_Sheet_The_Macro_Is_On).Select

My purpose of this Macro is that I going to run it on any sheet whose name may be "Sheet" + x. Or I may even get really fancy and name my sheet something meaningful. Either way, I need the code to reference the active sheet. I just don't know how to do that... And Google isn't being helpful.

Thanks for your time,

Mike


P.S. If there are any (free) VBA + Excel resources I can use, will you please link it?
P.S.S. This is just an annoyance issue: How do I get the Macro to save the location I place a graph on the worksheet? Right now, it sticks both graphs on top of each other, and I have to move it.

[MarvinP]

Hi Mike and welcome to the forum,

I believe you are looking for the ActiveSheet word.

Also Worksheets() can be called in an indexing method.
Worksheets(1).Name will return "Sheet1" or what you have renamed Sheet1
Worksheets(2).Name will return "Sheet2" etc

You need to spend some time in the Immediate window and start you line with a question mark which is an abbreviation for debug.print.

Then the word Application and then period. This will then give all possible next methods/verbs that can follow.

In the immediate window do"

Please Login or Register  to view this content.

When you type the period a drowdown will show and start typeing Worksheets( and it will show it wants an index..... The whole line looks like:

Please Login or Register  to view this content.

press enter and VBA will tell you stuff.
I hope this helps.
Also read. http://www.cpearson.com/excel/Debug.htm

[abousetta]

Hi Mike and welcome to the forum,

As for referencing sheets, there are several ways. For example:

with the sheet name

Please Login or Register  to view this content.

or

the order of the sheet

Please Login or Register  to view this content.

or just this sheet

Please Login or Register  to view this content.

As for free vba, there is a world wide web full of it. I would suggest starting with tutorials until you get the hang of it and then start searching for what you are after exactly. When you hit a bump in the road, then post what you have and what you want to achieve and usually someone will help you get from point A to B.

As for the charts, I see that you are using 2007 and I have read that Excel 07 stopped recoriding chart information with that release but brought it back with Excel 2010. Therefore (if this is correct), the macro recorder won't be of much help. If you have access to either 03 or 10 then maybe you could record the positions there and use the generated code. I'm not a big chart expert and so my response may not be the best.

Hope this helps.

abousetta

P.S. You almost never need to .Select or .Activate anything in vba code.

[EMyk08]

Thanks so much for the help everyone! It turns out that the 'ActiveSheet.Name' is just what I needed.

Thanks for the heads up on VBA resources.

And thanks for such a quick response.

Thanks!

[abousetta]

Most of us here on these forums are not top-level progamming experts, but we are eager to help whenever we can.

If you are satisfied with the responses, please mark the thread as Solved.

Good luck.

abousetta