+ Reply to Thread
Results 1 to 34 of 34

How to find which worksheet my macro is launched from

  1. #1
    Forum Contributor
    Join Date
    07-20-2015
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2016
    Posts
    303

    How to find which worksheet my macro is launched from

    I have a 4 workbooks with about 12 worksheets each - I have a macro that jumps between worksheets which i do by using the Worksheets command, so for instance the macro will have a command:
    Please Login or Register  to view this content.
    and the macro is then in the Fifth worksheet. Because I want to have only one macro work the same for all 4 workbooks I'd like to have the macro go back to the worksheet that launched the macro.

    Is there a way to store the beginning worksheet number and then return to that worksheet at the end of the macro?

    Example: The macro could be launched from worksheets 3, 4, or 5 and the last step in the current macro may finish in worksheet 9 and I need to go back to either 3,4 or 5 depending on which worksheet launched the macro. Thanks for any help you can give me

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,464

    Re: How to find which worksheet my macro is launched from

    At the beginning
    Please Login or Register  to view this content.
    At the end
    Please Login or Register  to view this content.
    Does that work for you?
    ChemistB
    My 2¢

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Contributor
    Join Date
    07-20-2015
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2016
    Posts
    303

    Re: How to find which worksheet my macro is launched from

    Yes!!! That worked exactly like I need it to.

    Thanks so much for the rapid solution

  4. #4
    Forum Contributor
    Join Date
    07-20-2015
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2016
    Posts
    303

    Re: How to find which worksheet my macro is launched from

    Hi ChemistB,

    As I said what you gave me worked perfectly, but I now have a need to determine the name of the or number of the worksheet. Can I interpret the value of "ThisOne" to determine what I need to do: For example

    If ThisOne is equal to Worksheets(5) select Worksheets(9)
    If ThisOne iw equal to Worksheets(6) select Worksheets(10)

    By the way I tried to add to your reputation and got a return message that I could not add to it because I had added to yours recently and must add to someone else before adding to yours again.

    Thanks

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,464

    Re: How to find which worksheet my macro is launched from

    VBA has changed some since Excel 2003 and my VBA is just adequete but try

    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    07-20-2015
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2016
    Posts
    303

    Re: How to find which worksheet my macro is launched from

    I guess it must be a excel 2003 problem because I get a compile error - syntax error with this code - The only thing I see different from your code is the worksheet number which should not be the issue?

    Please Login or Register  to view this content.
    The debugger hilites this line:
    Please Login or Register  to view this content.
    Thank you for your help

  7. #7
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: How to find which worksheet my macro is launched from

    As long as you do not 'select' or 'activate' you do not have to 'go back' because you never changed 'location'.

    In VBA you can perform any task without 'selecting' or 'activating'
    E.g.

    Please Login or Register  to view this content.



  8. #8
    Forum Contributor
    Join Date
    07-20-2015
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2016
    Posts
    303

    Re: How to find which worksheet my macro is launched from

    Thanks snb but I'm performing the same set of tasks in several worksheets from the one that launches the macro. (The macro can be launched from more than one worksheet. So I need to bounce between different worksheets depending on which one launches. So the code which chemistb gave me would be ideal if I could solve the syntax. Here is the code from chemistb that I am trying to get to work:

    Please Login or Register  to view this content.
    The debugger stops at this line:
    Please Login or Register  to view this content.
    Any help you can give is appreciated.

    Thanks
    Last edited by pongmeister; 11-20-2015 at 01:47 PM. Reason: to add additional info

  9. #9
    Forum Contributor
    Join Date
    07-20-2015
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2016
    Posts
    303

    Re: How to find which worksheet my macro is launched from

    I might add - if the macro is launched from Worksheet(5) then I only want the macro to perform it's tasks on Worksheet(5) and (8)
    also if launched from worksheet (6) then perform tasks in worksheets (6) and (9). etc

  10. #10
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: How to find which worksheet my macro is launched from

    if the macro is launched from Worksheet(5) then I only want the macro to perform it's tasks on Worksheet(5) and (8)
    Please Login or Register  to view this content.

  11. #11
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: How to find which worksheet my macro is launched from

    Quote Originally Posted by snb View Post
    Please Login or Register  to view this content.
    This method gets my vote.

  12. #12
    Forum Contributor
    Join Date
    07-20-2015
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2016
    Posts
    303

    Re: How to find which worksheet my macro is launched from

    Sorry for my limited knowledge of VBA but I don't know how to use this code. Will it allow me to do some updates on Worksheets(5) then go to Worksheets(8) and do some updates there then return to Worksheets(5)??

    What action does the copy workbooks(2).sheets(7).cells(40,3) statement do - If I enter this command and I'm in worksheet(5) will it take me to worksheet(8) - I imagine that is due to index+3, so would I assume if I want to return to sheet (5) from (8) I would put index-3 - Is that correct?

    Thanks again

  13. #13
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: How to find which worksheet my macro is launched from

    If you have read the posts meticulously: 'returning' assumes 'leaving'; if no 'leaving' takes place 'returning' is a void concept.

  14. #14
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: How to find which worksheet my macro is launched from

    Quote Originally Posted by snb View Post
    If you have read the posts meticulously: 'returning' assumes 'leaving'; if no 'leaving' takes place 'returning' is a void concept.
    That statement assumes that the person reading the posts understands what they are reading, and that the solution does all that is required, while I agreed with your suggestion earlier, I suspect that there might be more to the question than we are aware of.

    The first line of the OP's reply in post #12 suggests to me that there might be more to do than just copy and paste, in which case the code would require the sheet to be defined as variables, opened in 'With' blocks, or selected.

    pongmeister, please post all of your code, not just a couple of lines.

    The suggestion made by snb will work with copy and paste code without the need to select the sheets to copy to / from, so as stated, there is no need to return. However if you are trying to do other things on those sheets at the same time then the method will be different. Without seeing all that you are trying, we can only guess.

  15. #15
    Forum Contributor
    Join Date
    07-20-2015
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2016
    Posts
    303

    Re: How to find which worksheet my macro is launched from

    Yes, thanks. I am a novice using VBA and the majority of my code comes from the 'Record Macro' feature. Due to the size
    of my workbook I've had to cut down the number of worksheets in order to upload to the forum.
    Therefore the sheet numbers I need to affect will be different than the examples I put in the previous posts.

    I have commented out the code I had put in to flip flop between worksheets and put in a one line code to activate
    the new sheet in this shortened version of my workbook.

    Here's one of the sections of commented code:
    Please Login or Register  to view this content.
    In my full workbook I would like to determine which worksheet the macro is invoked from and depending on that answer I
    need to do do some copy/pasting and sorting in another worksheet then do some more copy/pasting/sorting/formatting in
    the initial worksheet. If that is do-able without activating the second worksheet that's fine with me. Keep in mind
    that If the invoking sheet is 5 then the alternate sheet is 8 - if invoking sheet is 6 then alternate sheet is 9 and
    if invoking sheet is 7 then alternate sheet is 10.

    Also, if invoking macro from sheet 5 only the cells in sheet 8 are manipulated/changed sheets 6,7,9 and 10 are not
    to be affected.

    Since I had to delete many of the worksheets in order get the book small enough to upload most of the formulas in the
    remaining sheets are not working.

    Thanks for all your help.
    Attached Files Attached Files

  16. #16
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: How to find which worksheet my macro is launched from

    Where are we going to find the code in your workbook?

    It would have been a good idea to remove the empty modules as well before posting.

    2 or 3 modules is understandable, but nearly 70?

  17. #17
    Forum Contributor
    Join Date
    07-20-2015
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2016
    Posts
    303

    Re: How to find which worksheet my macro is launched from

    Sorry, the code is in a macro named SET_TIEBREAKS. I have removed the empty modules but could not re-post my workbook since I don't see the selection button for posting??

    I don't know what causes empty modules - would you think i am doing something inadvertently to cause them?? I'd like to avoid having unused modules in the future.

    Thanks

  18. #18
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: How to find which worksheet my macro is launched from

    Pongmeister,
    just a couple of quick points:
    _ 1)
    Quote Originally Posted by pongmeister View Post
    .....but could not re-post my workbook since I don't see the selection button for posting??....
    _ make sure you “go advanced”:
    http://www.excelforum.com/members/da...ch-a-file.html

    _..........................
    _2) The code from Post #5 and #6 that is giving you problems:

    _2a) A simple typo is in those codes:
    You need
    ElseIf
    Not
    Else__If
    ( no space between )

    _2b) This
    Worksheets(5)
    is the same as
    Worksheets.Item(5)
    This means the 5th worksheet or 5th Tab counting from the left . Any code line of this type will error if that worksheet does not exist. In the file you uploaded you only had 3 worksheets, so you could only use up to
    Worksheets.Item(3)

    _.........................
    _3)
    Quote Originally Posted by pongmeister View Post
    .... I am a novice using VBA and the majority of my code comes from the 'Record Macro' feature. ....
    _ this explains why you have so many Active, Activate, Select, Selection, Scrolling around etc. etc. , and why you are continually talking about flip floping between worksheets , and 'returning' and 'leaving'. – that is all what you do whilst recording the macro!. You have to do that to “see” what to do. VBA does not:
    Quote Originally Posted by snb View Post
    ..... 'returning' assumes 'leaving'; if no 'leaving' takes place 'returning' is a void concept.
    .
    VBA just does what you want when you write codes like snb gave you. It does not need to activate and select to “see” what it is doing. ( In fact with a bit more effort you could probably do what you want without even opening Files. ( Although you would need to know all file and Sheet names for that ) ).

    Your code is horrendously unnecessarily complicated and it is a waste of both your time and anyone else’s trying to help you to continue to use it. Believe me i went through the same painful process whilst learning. It is worth the effort to make a recording of a shortened code that does some simple task such as a copy and paste of the form you want. Then simplify it after learning some VBA Basics, or post back again that code and ask for some help in simplifying it. That will help you get away from thinking as a person ‘going to’ , 'returning' , 'leaving' , ‘selecting things’ to do stuff with / on, and it will help you to think like what VBA does in order to make an efficient and understandable code.
    VBA organises that things are done. It does not need to go there , make it visible, active a worksheet etc etc...

    Here is a very simple example to get you started. Using you uploaded File, I recorded a macro that simply copied from Cell A1 from your first worksheet and pasted the content into cell AD100 in your second worksheet.
    Then I wrote a few simplified codes that do the same:

    Please Login or Register  to view this content.
    Check some of my earlier Thread posts for some better explanations of the different Copy and Paste methods used , for example:
    http://www.excelforum.com/excel-prog...ml#post4230540

    Alan
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  19. #19
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: How to find which worksheet my macro is launched from

    Quote Originally Posted by pongmeister View Post
    I don't know what causes empty modules - would you think i am doing something inadvertently to cause them?? I'd like to avoid having unused modules in the future.

    Thanks
    Recoding macros is the cause of that, I'm not sure of the exact process, but from observation I would say that the macro recorder is 'locked out' of a module once you make any changes to it.

    As Alan has already pointed out, recorded macros are bloated with a lot of unnecessary code to perform all of the actions you would whilst carrying out the procedure manually, but many of which do not need to be carried out when it is automated.

    It's ludicrously late (U.K. time), I'll have a look at cleaning your code up in the morning after sleep and recaffeination.

  20. #20
    Forum Contributor
    Join Date
    07-20-2015
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2016
    Posts
    303

    Re: How to find which worksheet my macro is launched from

    Ok, thanks much for your time and I will be able to load my workbook minus the empty macros now that Doc.AElstein gave me the info to use the "Go Advanced" button. I will do that tomorrow since I have a lot to review from all the info in this thread.

  21. #21
    Forum Contributor
    Join Date
    07-20-2015
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2016
    Posts
    303

    Re: How to find which worksheet my macro is launched from

    Thanks for all the info and I will start to attempt cleaning up the code as I learn more proper VBA.

    Regarding your 2B comment "_2b) This
    Worksheets(5)
    is the same as
    Worksheets.Item(5)
    This means the 5th worksheet or 5th Tab counting from the left . Any code line of this type will error if that worksheet does not exist. In the file you uploaded you only had 3 worksheets, so you could only use up to
    Worksheets.Item(3)"

    In my original workbook I do have over 10 worksheets but had to delete all but the 3 i loaded in the Sample book I uploaded (in order to get workbook size down to a loadable size. In the sample size I commented out the references to the workbooks in question and made active either sheet 2 or 3.

    Since it's 1:30 am my time I will review all this info and get back with more questions tommorrow.
    Attached Files Attached Files

  22. #22
    Forum Contributor
    Join Date
    07-20-2015
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2016
    Posts
    303

    Re: How to find which worksheet my macro is launched from

    Sorry it took a long time for me to get back - busy schedule.

    Thanks and I will work toward that goal of learning VBA Basics (any recommendation on a good online course or book on that??)

    Also I changed from else if to elseif in the SET_TIEBREAKS macr but got the following error:

    Run Time error '438'
    Object doesn't support this property or method.

    When I selected the "Debug" button the first line of the following code was hi-lited:

    Please Login or Register  to view this content.
    I realize from your comments about VBA that code will not be needed eventually but I'm just curious as to why it fails.


    Regarding the 4 simple macros you showed me I see that method should work very well for me, so if I understand correctly your code I'll use your
    names from your post and ask the following question: Since I have 4 workbooks with each containing 18 worksheets I would like to have one macro that will
    update specific worksheet.items depending on which module was active when macro was invoked (this to reduce the number of macros I need to
    create).

    My question is can I have a macro named "Controller" with an if statement so if it is invoked from the active sheet Worksheet 1 will run Makro2SHimpfGlified_1()


    or if it is invoked from active worksheet 3 it will run Makro2SHimpfGlified_2()

    ** I added a fourth worksheet for this question.

    So yoyour macros two would change to use worksheets 3 & 4 and would be invoked from the new "Controller" macro If that can be done:

    Please Login or Register  to view this content.

    Per your suggestion "It is worth the effort to make a recording of a shortened code that does some simple task such as a copy and paste of the form you want.
    Then simplify it after learning some VBA Basics, or post back again that code and ask for some help in simplifying it."

    I have recorded a simple macro: EXCEL_FORUM_SAMPLE_MACRO - Would you review and simplify it to get me started.

    Thank you
    Attached Files Attached Files
    Last edited by pongmeister; 11-25-2015 at 02:23 PM.

  23. #23
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: How to find which worksheet my macro is launched from

    Hi Barry “Pongmeister”,

    _1) Books, learning VBA etc.
    Quote Originally Posted by pongmeister View Post
    ....(any recommendation on a good online course or book on that??)
    ...
    The material currently out there is insanely overwhelming! – You can google search for ever. Often you need the experts in Forums like this to tell you where to search!

    _ . I am not too clued up on The best English books just now as I am in Germany. I did buy lots of different books, and found it OK occasionally to read through, but really , as most people will tell you, making the extra effort of practicing is really the only way to learn effectively. There is just so much to learn that no books can cover everything. And Often The documentation is never written anywhere because Bill Gates or whoever never got around to Documenting his work and he probably forgot anyway in the meantime what he wrote!!

    I also downloaded and let some you Tube Play List run in the background whilst practicing VBA, or as an alternative to Telly viewing evenings! – That is one easy way to learn, but again only second best to practice.
    Here some recent links to a thread on that:
    http://www.excelforum.com/excel-gene...s-for-vba.html
    Both You Tube links and a good reference for current books are given in that first link above.
    -Apparently ExcelForum has recently started a YouTube Channel,
    https://www.youtube.com/channel/UC92...NReLtq4jBWFrJw
    , but I have not had time to check that out yet – But it seems a random selection of Excel ( not VBA ) Videos – the list I give in the first above link, as well as a recent one i stumbled across
    https://www.youtube.com/playlist?lis...o2SEPBLLPGOyBz
    are all good organised VBA learning videos.
    Note. If the links I gave in the first link above do not currently go anywhere ( due to people reorganising their playlist locations etc.. ) then do a Google search with Search criteria “You Tube VBA” and the following Names, which are some of the good teachers that I can remember just now...
    YourProgrammingNetwork
    ExcelVbalsFun
    WiseOwlTutorials
    ExcelScreencasts
    ExcelVBAHelp
    Andreas Thehos
    LoeblComServices
    Dinesh Kumar Takyar
    VBA4Excel
    I-Helped-U DoubleCheck
    methodactor1982VBA

    ... and just google a bit and you should have an endless collection of learn material to choose from!! ( Please feedback with a link if you find any new good Play lists).
    Try to find their VBA Play lists rather than wading through the often enormous number of Excel Videos.

    I prefer to download the whole Play list and make with that a continuous running WMV , (Windows Media Video) File. But downloading / converting can take a while and the download/ converting software seems to get more difficult to use, with problems with downloading – lots of other junk coming down etc...( And i believe Microsoft have removed support for WMV – I have many older computers which I have, and like to use, WMV on ) . Running directly the video series through YouTube is OK, but you get lots of advertisements and have to keep hitting replay – When you have a WMV File you can get it to run continuously – i found it good to fall asleep to a running set of VBA Videos!! ) - Of course the alternative here is to buy some good learning Videos / DVDs. – I did that too, but again unfortunately all German and i am not up on good English ones, - but there are plenty out there..

    _. - My collected Video series is around 150GB so i cannot upload it anywhere for you!!!!

    ( By the Way, I am still a part time Amateur myself still learning. Answering Threads as soon as I could was another way I found very good in learning. – For example: Pick out some long unanswered Threads (hit the Unanswered Threads button) . - Sometimes you will find some Threads that are not technically challenging, but may take a while to answer – these are often left by the Pros here that answer very quickly many short but difficult technically challenging problems). Remember when you answer that the thread is no longer marked as unanswered so it will not be picked up by Pros who often start their day with hitting the Unanswered Threads button, - hence pick one out that is long unanswered that you think you can at least partly answer. )

    _. So learning is really difficult, and active participation in forums such as these is probably the best bet currently!!

    _ ...............................

    _2 Worksheet Finding Code error:
    Quote Originally Posted by pongmeister View Post
    ....I changed from else if to elseif in the SET_TIEBREAKS macro but got ..error....
    _2a) So.. The ElseIF compile syntax error we have sorted
    _2b) And... As long as we have enough Worksheets the “ index out of valid range “ error does not come up at run time. ( Note i have to guess the exact wording for errors as I am translating from my German Excel – so the actual wording in your English Excel may be a bit different)
    _2c) So Finally... let us try to get it to “work” to activate a Worksheet based on which is currently the ActiveWorksheet....
    I have never tried myself to check the Worksheet I am in.. I expect doing a code line of the form we are trying ( as suggested by ChemistB ) is not possible – So this ( Psuedo code)
    Is WorksheetObject1 = WorksheetObject2
    Is not possible. ( It surprises me that ChemistB suggested it as he knows much more than me – so maybe we may both be missing something obvious here. )

    This code will activate the next Worksheet along from that which is active at the start of the code running. It does a comparison of the item number ( Index) of the worksheet. So it does (Psuedo code)
    Is TheItemNumberofThisOne = 1
    Or
    Is TheItemNumberofThisOne = 2
    Etc.. etc...

    ( So It checks to see if the Item number on Worksheet Object “ThisOne” is 1, 2 or 3, then activates the next worksheet along. ( It is always good practice to have a final Else. In this code I have a final else to tell you that the active sheet was not one of the first 3.

    Code:
    Please Login or Register  to view this content.
    _..................................

    This code “works” at my end – I tried it on your last uploaded File !

    _3)..................

    I will answer your next questions in the next post as The Post size is limited!!

  24. #24
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: How to find which worksheet my macro is launched from

    _3)
    Quote Originally Posted by pongmeister View Post
    .... I would like to have one macro that will
    update specific worksheet.items depending on which module was active when macro was invoked...
    _3a)
    Your choice of words has me a bit confused here ( Note: i am a bit thick – that is not my fault... ).
    “worksheet.item() “ is, as i tried to explain, the number counting along from the left of a Worksheet in a workbook. (That is to say, As you see the Tabs below in the Excel Window counting from the left ). Maybe you meant to say “update specific worksheet things...” – I do not think you want to update ( change ) the item number of a sheet. ( This you can do with the following sort of code line:
    Please Login or Register  to view this content.
    Which in changing the position of the Tab will cause its item number to change, as well as changing others due to the total reorganised tab order. For example: Run this simple code on your last File:
    Please Login or Register  to view this content.
    ....)

    _.....
    _3b)
    Quote Originally Posted by pongmeister View Post
    ....
    My question is can I have a macro named "Controller" with an if statement so if it is invoked from the active sheet Worksheet 1 will run Makro2SHimpfGlified_1()
    or if it is invoked from active worksheet 3 it will run Makro2SHimpfGlified_2() .......
    As i mentioned i am still learning myself, and i think we are both maybe not quite grasping how VBA works and “thinks”. I am not sure if you really want a sheet to “evoke” a macro. A Worksheet can invoke a macro, - there is a special code type ( called “Event type” ) which i can do for you that springs in when you select a Worksheet. That is a little advanced.

    _ There is a lot to take in now... but you asked!!!!

    _ Each sheet in question would have a code of this form which MUST BE PUT IN THE SHEET MODULE, not a normal module. Then select the sheet in whose Sheet module you put the code in. Try it!
    Please Login or Register  to view this content.
    _......................

    _ 3c) . when you get that working and understand what is going on, then try to take it one step further:
    _3c) (i) Copy this code to the SHEET MODULE of both your first and Third Sheet ( Delete the initial code above first, or overwrite it with the following code )

    Please Login or Register  to view this content.
    _ 3c) (ii) Copy all these codes to ANY NORMAL MODULE:

    '
    Please Login or Register  to view this content.
    _...

    As I am trying to demonstrate as much as possible along the lines of the codes discussed so far, I modified your file so that the cells A1 and AD100 originally look like this

    Using Excel 2007
    Row\Col
    A
    1
    Originally Written in cell A1 sheet item 1
    ca



    Row\Col
    AD
    100
    CA_RR1



    Row\Col
    A
    1
    Originally Written In cell A1 in Third Sheet
    CA_RR1_TB



    Row\Col
    AD
    100
    Special


    _ ..
    If now you select, for example, the first Worksheet, ( “ca” ) and then look at the second Worksheet ( “CA_RR1” ) then you should find that the second Worksheet now has changed to look like this

    Row\Col
    AD
    100
    Originally Written in cell A1 sheet item 1
    CA_RR1
    _..
    Similarlar results are obtained after selecting the Third sheet
    _........
    That was a lot to take in. I tried to keep it as simple as possible and as close to your request as possible. But VBA can be a pain in the ar__ sometimes. You will see for example i have a pair of extra lines in the SHimpfGlified codes:

    Please Login or Register  to view this content.
    For some reason ( which i do not understand) , the lines
    .Paste
    Or
    .PasteSpecial
    Caused the WorksheetActivate code to spring in again ( causing VBA to go into an endless loop!! ) . So the purposes of those two code lines is to temporarily disable codes of this “Event” type.

    _...........

    _3d) You may not want to do this bit until you have the above understood. But for my later reference, I thought i would do it now while i am “here”
    This involves a further method to do something similar to Copy Paste, should only values and no Format be required to be copied. You see this is a step further, as it does not look anything like what a Macro recording gives, but can be the most efficient method:
    The basic idea is just to put the values of a cell or cells, into other cell or cells. So for example, once you have all the codes from this Post working, then make the changes to the codes in the normal module Thus:

    For
    Sub DetermineActiveSheetThenUpdateParticularSheet(), make the following changes shown in red ( which simply Calls the new codes:

    Please Login or Register  to view this content.
    And
    Copy these two new codes to the normal module

    Please Login or Register  to view this content.
    _. These codes give the same result as in 3c) ( And note i did not seem to need the .EnableEvents = False “Bodge” for these codes – i expect the Copy Paste stuff somehow activates the sheet “internally” whereas the assigning Values does not. - Possibly the codes
    Sub Makro2SHimpfGlified_3()
    And
    Sub Makro2SHimpfGlified_4()
    ( Which incidentally by pass the Clipboard ) may also not need the bodge – A bit of Homework for you to try maybe. – As always before trying any VBA code, save your worksheet just before. You have 3 possibilities then ( in this order ) to stop a VBA infinite loop – 1 Esc ; - 2 Ctrl + Break ; finally – 3 if all else fails, restart computer!!! ) ).
    ( Note before testing make sure you empty/ clear what was already copied to the AD100 cells in codes in 3c) )

    _3e) As regards
    Quote Originally Posted by pongmeister View Post
    .....and would be invoked from the new "Controller" macro If that can be done:...
    _ .. if you have followed what is going on so far, you may see that each
    Private Sub Worksheet_Activate()
    Is effectively your "Controller" macro. So if you choose yourself to run ( “evoke” ) this "Controller" macro when required rather than it being “ evoked” by selection of the sheet, then rename one of those two codes to:
    Sub Controller()
    And then copy that to a normal module. And delete the two Private Sub Worksheet_Activate Codes.
    Then when you are in any sheet, run the macro
    Sub Controller()
    Then it will perform the same results. I expect you could possibly think or say that this is “evoking “ the macro from any sheet, as you could say or think when you are looking at an ActiveSheet then you are “there” so you have “evoked” it from there. But this comes back to the original point snb tried to get across. He is an expert who really knows how VBA works ( very few people do ) and I expect what he was trying to say is that because of how VBA “works” , when you run a normal macro it “runs” – period! It does not run from “where “ you were – when you run a macro the keyboard strokes you make in order to do that are independent of, and have nothing to do with, what sheet happens to be Active – For example if you are selecting / “evoking” from the ribbon at the top, that is the only ribbon – you do not have a ribbon for each sheet ( I think!?)


    _4) I answer this in the next post due to Post size limitations. !!

  25. #25
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: How to find which worksheet my macro is launched from

    _4)
    Quote Originally Posted by pongmeister View Post
    ......
    Per your suggestion "It is worth the effort to make a recording of a shortened code that does some simple task such as a copy and paste of the form you want.
    Then simplify it ....... or post back again that code and ask for some help in simplifying it.".......
    .... I have recorded a simple macro: EXCEL_FORUM_SAMPLE_MACRO ......
    OK. I did a quick simplification. But Note I had a bit of difficulty with the example you gave as
    _4a)(i) the sorting bit did not actually seem to do anything. !!
    And
    _4a)(ii) The Pasting in your code pastes out formulas which gives empty Cells (At my end – may have given different results at your end- ( see note _4e(i) anf _4e(ii) below ) !!
    So it is very difficult for me to know exactly how to simplify something that does not appear to do anything. ( Other than empty some cells in the sheet “CA_RR1” !!)

    _ .. but anyways:-
    _4b) I have done some simple mods involving removing the unnecessary Selecting / Selection as discussed previously. These such mods are almost always valid to do
    _4c) I simply missed out some arguments that I do not understand and did not appear to do anything. Some, for example, may be the default options, so do not need to be given. Like many people, even Pros, things like arguments are so many and so badly documented that it is often a case of Recording a macro, then googleing / checking what the various Arguments mean and determine by that and a bit of experimenting if they are needed. So you may need in the future to look a bit more in detail at all the arguments***, possibly needing in the future to put some that i removed back - you rearely need all - ( but note my point _4d) )
    ...Because of points _4a)(i) and 4a)(ii), it was difficult for me to check what exactly you were trying to do!

    (– ***Check this out to get the Arguments you need for a particular Range Sort case
    https://msdn.microsoft.com/en-us/lib...ange.sort.aspx
    ....
    .)

    _4d) I may just note in passing that I do personally tend to include all options, even if they are default ones, as it helps me to see what is going on.


    _.............
    So this is the code you supplied:

    Please Login or Register  to view this content.
    _.....
    This is a bit of a guess of codes that would do the same, ( keeping for now close to your chosen Copy Paste method ) :

    Please Login or Register  to view this content.

    _4e) It may be wise to try again and give me a code to simplify that does something more specific so that i can easier check that it still does the same after simplifying: Note if you do that,
    _4e)(i) Check that your Range Sort, or whatever you choose to do, does, or rather changes, something

    _4e)(ii) the VBA Worksheets Paste Method ( used in my
    Sub Makro2SHimpfGlified_1() and in your sample code )
    Is one of two Copy Paste Methods which uses the Clipboard. In this Method, VBA tries to guess what you want to do ( for example copying values or formulas and / or Formats etc... ) – and this may give different results randomly!! ( VBA holds different versions of the Copied data in the clipboard ).....
    http://www.mrexcel.com/forum/excel-q...ml#post4043472
    .....)

    The VBA Range Paste Method, ( sometimes referred to as the “PasteSpecial Method” ) ( used in my
    Sub Makro2SHimpfGlified_2() )
    Is the other Copy Paste Method which also uses the clipboard, but by virtue of the argument
    Paste:=xl________
    allows you to specify exactly which version of the Copy held in the Clipboard is then used ( Pasted ). This second method can take longer, but is maybe wiser to use when learning ). ( In my example i chose it to paste out values (......
    Paste:=xlPasteValues
    .....) , but there are many other options:....
    http://www.mrexcel.com/forum/excel-q...ml#post4071766


    _ ....)


    _................................................



    Hope i have not confused you too much. You asked a lot and i did my best to explain as clearly as i could!! ( I almost certainly have some typos!! ) . I will help further if / when I can. Let us know how you get on please. I will be happy to return a file with all codes in finally, but i think in the long run it is better if you try first to adapt the codes to your file yourself: “Practice is the best way to learn!”

    Alan Elston
    Bavaria
    Germany

  26. #26
    Forum Contributor
    Join Date
    07-20-2015
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2016
    Posts
    303

    Re: How to find which worksheet my macro is launched from

    That's a lot of info you've given me. Thank you so much for all the time you must have put in on this. I have read thru your posts and am absorbing and trying them out as i go. The code you gave me in this post ie:

    Please Login or Register  to view this content.
    Worked great!! So it solved my immediate issue, but I will still need to continue working on understanding the VBA code.

    I will answer each post in order and may take some time, although I have looked at them and believe I have a general understanding of your solutions.

  27. #27
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: How to find which worksheet my macro is launched from

    Quote Originally Posted by pongmeister View Post
    ..... Thank you so much for all the time ....
    I will answer each post in order and may take some time, although I have looked at them and believe I have a general understanding of your solutions.
    Your welcome, thanks for the feedback.

    Take your time, - i am only occaisionally "here" just now, but will answer any questions you have if and when i can.

  28. #28
    Forum Contributor
    Join Date
    07-20-2015
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2016
    Posts
    303

    Re: How to find which worksheet my macro is launched from

    Hi Doc.AElstein

    I have set up a test to use a controller macro to do a copy/paste operation. This is just so I can work on simplifying
    my code. I used your sample code as my basis and set up a workbook with 6 worksheets. The controller should work as follows:

    I am trying to copy contents of A1 from a worksheet to another worksheet in cell B1 depending on what the active sheet is
    when I run the macro.

    If Active Sheet is
    1 then copy to sheet 4
    2 then copy to sheet 5
    3 then copy to sheet 6

    When I run the macro named "SET_TIEBREAKS_CONTROLLER"
    I get confusing results:

    If active sheet is sheet 1 the copy of A1 is pasted into sheet 1 (incorrectly - should be to sheet 4)
    If active sheet is sheet 2 the copy of A1 is pasted into sheet 5 (correctly)
    If active sheet is sheet 3 the copy of A1 is pasted into sheet 5 (correctly)

    I single stepped through the macro and it goes thru the correct sub-macro so I don't see where it is failing to perform
    correctly.

    Here's copy of macro code
    Please Login or Register  to view this content.
    Would you take a look and see if you can spot what's going wrong.

    Thank you
    Attached Files Attached Files
    Last edited by pongmeister; 11-30-2015 at 03:04 AM.

  29. #29
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: How to find which worksheet my macro is launched from

    Hi Barry:

    _1) I guess this is a typo,
    Quote Originally Posted by pongmeister View Post
    ....
    If active sheet is sheet 1 the copy of A1 is pasted into sheet 1 (incorrectly - should be to sheet 4)
    If active sheet is sheet 2 the copy of A1 is pasted into sheet 5 (correctly)
    If active sheet is sheet 3 the copy of A1 is pasted into sheet 5 (correctly)...
    ..but I think I get the point of what you want and what is going on.

    _2 ) Sub Makro2SHimpfGlified_1() Problem
    In some of the early codes I gave I think I was a bit careless, - when using the Worksheets Paste Method. This Method principally, or in its basic mostly commonly used form, works on a Sheet, and pastes from the clipboard with the Top Left of the current Selection used as the reference of “where” to paste to.

    Hence you usually see it used in this form ( In conjunction with the Copy Method ) ( Psuedo Code )

    A_Range.Copy’ VBA Range Copy Method

    A_Worksheet.Paste’ VBA Worksheets.Paste Method

    However i am using it with a not typically used ( For this method at least ) argument
    Destination:=
    Which allows you to specify where the Top Left of where you want to paste out is.
    And In some codes, I did
    Destination:=Range(“___”)
    That is bad practice to do that, relying on the Implicit Default of which Worksheet the
    .Range
    Applies to. Usually it is the Active sheet. Hence in your case it pasted back out to Worksheet 1, as this was Active at the time!

    I should have done ( pseudo code )
    Destination:=A_Worksheet.Range(“___”)

    This is a good demo of why not too rely on Implicit Defaults. ( Most people do, even Pro’s, as it shortens code. But as you see it can come back and bite you in the bum )

    ( BTW....., The above Problem would also come up in
    Sub Makro2SHimpfGlified_2()
    If i had done this
    Range("B1").PasteSpecial Paste:=xlPasteValues
    instead of
    Worksheets.Item(5).Range("B1").PasteSpecial Paste:=xlPasteValues
    .....)

    _.............................

    Here the modified correct code


    Please Login or Register  to view this content.

    Sorry about the mistake. ( I could say it was a deliberate one to emphasis the Point of not relying on Implicit Defaults – as i said pros do that and also get caught out......).


    Alan

    P.s. Email Notifications are not working for Replies, so I PM’ed you about this reply – hope you get it
    A good Tip at ExcelForum is to visit the site from time to time and hit the Settings, or User CP ( User Control Panel , UCP) Button
    http://www.excelforum.com/usercp.php
    to check out if you have replies. Or just check out the thread from time to time as the UCP doesn’t work either sometimes, like much of the Forum Software these days!!!......

    P.P.s
    Did you get an Email Notification of this reply and / or a Email Notification of my PM to you ?
    Last edited by Doc.AElstein; 11-30-2015 at 06:02 AM.

  30. #30
    Forum Contributor
    Join Date
    07-20-2015
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2016
    Posts
    303

    Re: How to find which worksheet my macro is launched from

    Yes, I did get your PM to me - I wasn't aware of that option but now I have read it and applied your change and it did work as expected. I'm still confused as to why a different form of the paste was necessary between in Makro2SHimpfGlified_1() and Makro2SHimpfGlified_2()

    Ie In 1 the paste was
    Please Login or Register  to view this content.
    and in 2 the paste was
    Please Login or Register  to view this content.
    Is the syntax different because 1 does a paste and 2 does a paste special??

    Thank you

  31. #31
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: How to find which worksheet my macro is launched from

    Hi Barry, good too here from you
    Quote Originally Posted by pongmeister View Post
    .... I'm still confused as to why a different form of the paste was necessary between ......
    Is the syntax different because 1 does a paste and 2 does a paste special??
    ...
    _. I think you almost have it....
    _ .. Basically you are not doing different form of the paste, unless you mean loosely by paste that you are “putting in the worksheet range”
    Paste has a specific meaning in VBA and PasteSpecial a similar but different meaning:


    I tried to explain this in the link i gave in post #15......
    http://www.excelforum.com/excel-prog...ml#post4230540
    _ ..........well as in this bit from the last post , Post # 29
    Quote Originally Posted by Doc.AElstein View Post
    ..... when using the Worksheets Paste Method. This Method principally, or in its basic mostly commonly used form, works on a Sheet, and pastes from the clipboard with the Top Left of the current Selection used as the reference of “where” to paste to.

    Hence you usually see it used in this form ( In conjunction with the Copy Method ) ( Psuedo Code )

    A_Range.Copy’ VBA Range Copy Method

    A_Worksheet.Paste’ VBA Worksheets.Paste Method

    However i am using it with a not typically used ( For this method at least ) argument
    Destination:=
    Which allows you to specify where the Top Left of where you want to paste out is.
    ..........


    _ . maybe flick through that again quickly..now, or later...but...

    _ ... .. there was a lot to take in. And I sympathise as this point you bring up is one that caught me out time and time again until the penny finally dropped with me. So I will try again to explain fully.

    _ 1) Quick OOP ( Object Orientated Programming) review
    _ Skip this bit if you already know, or it confuses you more.
    _. As I understand ti , behind the words you type in VBA are lots of more complicated classic type code lines. The idea is to make it easier for us such that VBA works as if it is an OO Program language. The idea with that is a sort of parallel with real life “Stuff” or “Objects” and how you handle and use these things.
    A classical example given is a Car and a Moped. A Ferrari could be thought of as belonging to a Class of Car. But straight away it gets complicated. Maybe it could also be a class of a motor vehicle, and in that Class a Moped could be included!
    Anyway, going sort of “down” or “along to the right in a chain” of a “Hierarchy” of these things.... we could say this to paint a wing mirror Purple ( All “Pseudo” Codes )

    Ferrari.WingMirror.Color.Purple=Purple
    _ . in OOP Jargon:
    Object.Property.Property.Property=
    ( But note the first Property here returns a new object, WingMirror, to which a Further Property is applied

    Or to find out what the color is
    Let TheColorIwant= Ferrari.WingMirror.Color
    _. In OOP Jargan
    = Object.Property.Method.
    ( The Property Wing Mirror returns a Wing Mirror Object and the Method Color gives a String back with the color name )

    What all complicates the issue is
    _(i) Bill Gates probably forgot, or lost interest, and maybe could not be bothered ( as is often the case with the best Engineers ) to document it at the time.. Not surprisingly many experts with lots of experience tell me the Help in Excel gaets progressively worse in newer Excel Versions
    _(iI) As you may be able to see here, Properties and Methods are sometimes given the same name and/ or the same Property belonging to a different Object, can be given the same name for a different object. Like in our example the wing mirror

    _ (iii) ( related to (ii) ) Some things now called Properties were earlier referred to as Methods, and again no one knows or remembers everything!
    In our example
    Ferrari.WingMirror.Color.Purple=
    Might have been once referred to as
    Object.Property.Property.Method


    _ ..........................


    _ 2) ...............Part 2) of this “story in” the next Post

  32. #32
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: How to find which worksheet my macro is launched from

    _2)
    Quote Originally Posted by pongmeister View Post
    .... I'm still confused as to why a different form of the paste was necessary between in Makro2SHimpfGlified_1() and Makro2SHimpfGlified_2()

    Ie In 1 the paste was
    Please Login or Register  to view this content.
    and in 2 the paste was
    Please Login or Register  to view this content.
    Is the syntax different because 1 does a paste and 2 does a paste special??.....
    Yes. That is basically it

    The key thing to understand,
    (or failing that, for now to memorise until, like me, after a bit of experience the “penny drops” is in 2a)(i) and 2a) (ii) below. )

    _ 2a) You have 2 distinct different “Things” here
    _2a)(i)
    Paste
    Is a Method belonging to the Worksheet Object
    https://msdn.microsoft.com/de-de/lib.../ff821951.aspx
    So ( “pseudo” Code )
    ObjectWorksheet.Paste

    _2a)(ii)
    PasteSpecial
    Is a Method belonging to the Range Object
    https://msdn.microsoft.com/de-de/lib.../ff839476.aspx
    So ( “pseudo” Code )
    ObjectRange.Paste


    _2b(i) With Paste you Fundamentally must Specify just a sheet. The top left corner of where you Paste will be taken by default as the Top left of the current Selection in the Current Active sheet.
    BUT you can change that default range by using the optional Argument, Destination:=
    So ( “Pseudo” code )
    Please Login or Register  to view this content.
    And be careful not to make the mistake I did, and fully qualify in which sheet that range is
    Please Login or Register  to view this content.

    _2b)(ii) Fundamentally you must specify a Range with PasteSpecial.
    And again remember to be explicit
    AWorksheetObject.Range.Paste
    ( Note if you understood _1) you might see that .Range here could be regarded as a Property ( or Method) , Not an Object, but after working on that AWorksheetObject , it returns a new Object, a Range object, (8 which is required for the syntax of the PasteSpecial Method )

    Let me know if you need more help.
    Alan


    P.s. Regarding Email notifications. Can you tell me please:
    _1) Did you get an Email Notification of my PM, and if so roughly when
    _2) Did you get Email Notification of my reply , Post #29, and if so roughly when ( You should have had it last Monday, a couple of hours after you posted Post #28 )

  33. #33
    Forum Contributor
    Join Date
    07-20-2015
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2016
    Posts
    303

    Re: How to find which worksheet my macro is launched from

    Thanks Doc, Sorry I took so long to get back but I have to finish preparing my workbook to handle and upcoming tournament. So I did get the logic in to switch back and forth between worksheets. I know that's not the preferred method but will work fine until I can incorporate the VBA basic codes I need.
    I'm looking at an online site now that gives some syntax on the vba commands and need time to incorporate them. I will mark this thread closed for now.

    Thanks again for all of you excellent assistance.

  34. #34
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: How to find which worksheet my macro is launched from

    Hi
    Quote Originally Posted by pongmeister View Post
    ...................
    You're welcome
    Thanks for the Feedback

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. PrintPreview won't come take focus after being launched by VBA Macro
    By DanielP86 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-18-2013, 06:42 PM
  2. [SOLVED] Macro launched with keyboard shortcut breaks after a "File Open" action
    By Skotzmun in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-04-2013, 03:46 AM
  3. For loop not being launched
    By Leia in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-09-2011, 06:21 AM
  4. Replies: 3
    Last Post: 06-27-2008, 11:34 AM
  5. [SOLVED] macro launched when condition is true
    By Dariusz Tomon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-24-2006, 11:35 AM
  6. converting ppt to pdf launched from xls
    By pm in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 01-06-2006, 07:10 PM
  7. [SOLVED] converting ppt to pdf launched from xls
    By pm in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-05-2006, 07:35 PM
  8. hide a worksheet so that a macro can still find it
    By frendabrenda1 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

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