+ Reply to Thread
Results 1 to 14 of 14

VB CODE Running from active sheet

  1. #1
    Forum Contributor
    Join Date
    12-21-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    387

    VB CODE Running from active sheet

    Hi,

    I have a VB code in my workbook "DATAExp_CF".
    here is a code
    Please Login or Register  to view this content.
    I want to run this code in my active sheet in other workbook..

    this code will run in my open workbook Sheet1 is instead of "Exp_CF" & Sheet8 instead "B-F Trade"

    Can anyone help with how to go about this as I have no idea really!
    Last edited by hkbhansali; 01-28-2018 at 07:36 AM.
    My English is very poor, so please be patient >_<"

    Thanks & Regards.
    hkbhansali

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: VB CODE Running from active sheet

    Do you want to run this code only in the active workbook Sheet1 and Sheet8? If so make the changes in red:
    Please Login or Register  to view this content.
    Note that Sheet8 must have named ranges called "Criteria" and "Extract".

    The line in blue must also be changed but it does not explicitly say what sheet it applies to so I don't know what to change it to.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Contributor
    Join Date
    12-21-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    387

    Re: VB CODE Running from active sheet

    Hello 6StringJazzer,

    Thanks for help me..
    I have changed my code but its not working.. new code

    Please Login or Register  to view this content.
    Sheet 8 have both name ranges

    Code in blue line is applied in Sheet8

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: VB CODE Running from active sheet

    References to the other workbook must be explicitly qualified. See additional changes in red.
    Please Login or Register  to view this content.
    Last edited by 6StringJazzer; 01-28-2018 at 10:58 AM.

  5. #5
    Forum Contributor
    Join Date
    12-21-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    387

    Re: VB CODE Running from active sheet

    Hi, 6StringJazzer,
    I have applied code but it can't work

    Please Login or Register  to view this content.

  6. #6
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: VB CODE Running from active sheet

    Please explain what "can't work" means.
    • Does the code run?
    • Does it run but do nothing?
    • Does it produce error messages? If so, what do the messages say?
    • Does it produce unexpected/wrong results? If so, how do the results differ from what you expect?
    • Does it hang?

  7. #7
    Forum Contributor
    Join Date
    12-21-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    387

    Re: VB CODE Running from active sheet

    Run time error 438
    objects doesn't support this property or method

    Debug

    And code get yellow line (here mark with blue line)

    Please Login or Register  to view this content.
    Last edited by hkbhansali; 01-28-2018 at 01:08 PM.

  8. #8
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: VB CODE Running from active sheet

    I don't think I can help further without seeing your files. Can you attach the file that has Sheet1 and Sheet8?

  9. #9
    Forum Contributor
    Join Date
    07-23-2016
    Location
    Texas
    MS-Off Ver
    2016
    Posts
    273

    Re: VB CODE Running from active sheet

    Try something like the below where the worksheets is passed to the macro along with whatever other variable parameters you want. The macro can be in any workbook module.

    Please Login or Register  to view this content.

  10. #10
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: VB CODE Running from active sheet

    Quote Originally Posted by EssoExplJoe View Post
    Try something like the below where the worksheets is passed to the macro along with whatever other variable parameters you want. The macro can be in any workbook module.

    Please Login or Register  to view this content.
    Note that the OP does not want to use B-F Trade, which is hard-coded here. There would need to be two Worksheet parameters for the Sub. Also note that in teh third from last line of code, all uses of Range and Rows have to be qualified with ws.

  11. #11
    Forum Contributor
    Join Date
    07-23-2016
    Location
    Texas
    MS-Off Ver
    2016
    Posts
    273

    Re: VB CODE Running from active sheet

    He can also pass the B-F Trade to the macro as a string variable.

  12. #12
    Forum Contributor
    Join Date
    12-21-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    387

    Re: VB CODE Running from active sheet

    Hello 6StringJazzer, and EssoExplJoe

    Thank you very much you both have help me..
    Finaly my code working..

    Sub MacroCF1()
    '
    ' MacroCF1 Macro
    '

    '
    Sheets(1).Range("B2:G22000").AdvancedFilter Action:= _
    xlFilterCopy, CriteriaRange:=Range("Criteria"), CopyToRange _
    :=Range("Extract"), Unique:=True

    Range("G2:O2").AutoFill Range("G2:O" & Range("A" & Rows.Count).End(xlUp).Row)

    Range("A2").Select

    Deletemismatch

    End Sub

  13. #13
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: VB CODE Running from active sheet

    If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

    If a member helped you solve your problem, consider adding to their reputation by clicking on the star icon addreputationicon.jpg below their name.

  14. #14
    Forum Contributor
    Join Date
    12-21-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    387

    Re: VB CODE Running from active sheet

    Hello 6StringJazzer,

    I am sorry I forgot to Solved thread ...

    now Mark as Solved..

+ 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. Replies: 6
    Last Post: 11-29-2017, 03:00 AM
  2. Active Workbooks Displayed before Running Macro Code?
    By swade730 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-03-2014, 10:41 AM
  3. [SOLVED] Running Macro from another sheet but referring to Active Worksheet
    By RUTrading in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-06-2014, 06:27 PM
  4. Code for makro running automatic every .... minutes (if active)
    By kanopat in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-06-2014, 10:57 AM
  5. [SOLVED] Active Sheet not automatically running macro
    By RichTea88 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-10-2014, 06:54 AM
  6. Enter key moves active cell unexpectedly after running code
    By GCW esq in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-26-2011, 04:43 AM
  7. Running code in one open workbook another active
    By shumba in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-14-2010, 04:20 AM

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