+ Reply to Thread
Page 2 of 3 FirstFirst 1 2 3 LastLast
Results 16 to 30 of 34

How to find which worksheet my macro is launched from

  1. #16
    Banned User!
    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?

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

    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

  3. #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 )

  4. #19
    Banned User!
    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.

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

    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.

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

    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

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

    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.

  8. #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!!

  9. #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. !!

  10. #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

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

    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.

  12. #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.

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

    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.

  14. #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.

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

    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

+ Reply to Thread
Page 2 of 3 FirstFirst 1 2 3 LastLast

Thread Information

Users Browsing this Thread

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

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