+ Reply to Thread
Results 1 to 11 of 11

Using a variable to address different worksheets

  1. #1
    Registered User
    Join Date
    09-27-2012
    Location
    Monticello, Florida
    MS-Off Ver
    Excel 2010
    Posts
    12

    Question Using a variable to address different worksheets

    I have a workbook that has 13 worksheets with tab names like "7-4 13186" and "7-11 13193" that represent weeks in a quarter. The 14th sheet would be a summation of the same cell from each worksheet. For example, on the totals sheet:

    Please Login or Register  to view this content.
    These are the actual names of the worksheets for the 3rd quarter. My problem is, this code will only work with this one quarters data. I use a macro to create new tabs for each quarter, so the 4rth quarter will begin with "10-3 13277", and next is "10-10 13284".

    How would I use a variable for the names of the tabs so I could use the macro to fill in each total sheet?

    If it helps, I have a list of the names in a worksheet titled "Drop Down Lists" cells C39:C51; this is the source where the sheets are originally named. In addition, there are quite a few sheets total up to this point. I'll try to attach a screen shot showing the sheet listing.

    Also, this is the code I'm using:

    Please Login or Register  to view this content.
    Any and all help is GREATLY appreciated!!

    Thanks,

    Mike - Accountant II



    p.s., I'll be changing jobs on Friday, so I'm kind of in a bit of a hurry to complete this by close of business Thursday 10-11-2012 - Thanks again!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,588

    Re: Using a variable to address different worksheets

    If the sheets are next to each other you can do this cheap trick.

    You just need to enclose the sheets you want to sum with two empty sheets, I called them Start and Stop.
    Then in the sheet where you want the result a formula looling like this: =SUM(Start:Stop!C3) will do it. Summing C3 across the sheets.
    Attached Files Attached Files
    Last edited by Jacc; 10-09-2012 at 02:00 PM.
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  3. #3
    Registered User
    Join Date
    09-27-2012
    Location
    Monticello, Florida
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Using a variable to address different worksheets

    Jacc - this is an interesting idea - I really appreciate your providing the example! I'll give it a go & see how it works out.

    I really want to learn the syntax for using variables too, so I hope some others will help out.

    Thanks again!!

    Mike - Accountant II



    I see it includes the Start and Stop sheets also.
    Last edited by Accountant II; 10-09-2012 at 02:17 PM.

  4. #4
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,588

    Re: Using a variable to address different worksheets

    You're welcome! Of course, if a sheet ends up outside the Start and Stop sheets it will be excluded. You can test that by just moving the sheets around in my workbook.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,366

    Re: Using a variable to address different worksheets

    this is a very basic version of what i think you want. assume E2 contains the name of your sheet, and you want to reference B2 on that sheet...

    =INDIRECT(E1&"!B2")

    you can build on that using offsets, counts and many other functions. play with it and see what you come up with
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Registered User
    Join Date
    09-27-2012
    Location
    Monticello, Florida
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Using a variable to address different worksheets

    I appreciate the reply!

    Out of curiosity, would it be possible to assign variables like start = 'Drop Down Lists'!C39 and end = 'Drop Down Lists'!C51 and then do something like:


    Please Login or Register  to view this content.
    I know this syntax isn't right, but in plain old Excel, this would be what I'm after - a generic/variable way to address the tab names.

    Thanks!

    Mike - Accountant II

  7. #7
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,588

    Re: Using a variable to address different worksheets

    Here I added some generic code that builds generic versions of the formula you want.
    It builds the formula from the list of sheet names in the listsheet and puts the formula in A2 in the sum sheet.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    09-27-2012
    Location
    Monticello, Florida
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Using a variable to address different worksheets

    Jacc,

    Many thanks for the code - When I get back in to work tomorrow, I'll try to follow what you provided!! I'm not very good at following loops & that kind of thing, but I enjoy trying.

    Again, if I'm unable to understand it, I can always fall back on your "cheap trick" - I could just insert the Start and Stop worksheets and use your formula.

    You folks are to be commended for your willingness to put up with us newbies! You have my sincere gratitude my friend!

    Thank You!


    Mike - Accountant II

    (I hit your * again, but since I did it the first time, it won't let me * you again.)


  9. #9
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,588

    Re: Using a variable to address different worksheets

    Thanks! To be honest I'm a bit lazy, I didn't bother taking a proper look at your code until now.
    Once I did look at it, it wasn't that hard to insert part of the code I wrote. You just have to test it.

    You will learn to write VBA stuff like this if you are interested and keep fiddling with.

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    09-27-2012
    Location
    Monticello, Florida
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Using a variable to address different worksheets

    Jacc,

    Due to a lack of time (tomorrow is my last day in this position), I went ahead and used your =Sum(Start,Stop!F2) formula and it works perfectly for what I'm doing.

    I want to thank you for taking the time to help me!! I love learning VBA (or should I say 'trying to learn' VBA). I bought 3 books on Excel 2010 w/VBA. One from Walkenbaugh, and two from Bill Jelen. My problem is time. At over 800 pages long, I just don't have the time to hunt through these books to find the tricks to properly set up the syntax. Plus, at 55, unfortunately, it just doesn't really stay in my memory!

    A prime example would be the following lines of your code:

    Please Login or Register  to view this content.
    I can see you are concatenating certain items, but how do you know/remember that there is an apostrophe following the = and + signs, and leading the ! sign? The syntax is very confusing to me.

    In any case, I would like to give you a sincere THANK YOU my friend!!

    Mike – Accountant II


  11. #11
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,588

    Re: Using a variable to address different worksheets

    You are welcome! Yeah those apostrophes are there just in case.
    Whenever you make a formula referencing other sheets they pop up automatically if the name of the sheet contains a space.
    In Sheet2 of an empty workbook just type =Sheet1!A1 in a cell. Then rename Sheet1 to Sheet 1 or something and go back and check the formula. It will have updated itself with apostrophs. Most likely still there for backward compability. At the beginning of time space was considered an unnecessary luxury apparently. :-)

    The books are not to be read from first to last page, use them to lookup things and as coffee table books, that's what I do.
    VBA knows no age. I'm in my late 30's and while some VBA stuff stick no problem other VBA stuff refuses to stick, I have to google and lookup over and over again. I also run a LOT of small test macros to figure out how things work. And the forum of course.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1