+ Reply to Thread
Results 1 to 2 of 2

Modify VBA to work in a worksheet module

  1. #1
    Forum Contributor
    Join Date
    09-21-2014
    Location
    Midwest USA
    MS-Off Ver
    2010
    Posts
    349

    Modify VBA to work in a worksheet module

    The following code works in the Immediate Window as well as a regular module.
    Please Login or Register  to view this content.
    It identifies the next blank row within an Excel table on worksheet “Combined.” However, I need that row number in the worksheet code for a different worksheet and when I try and use it there I get a 1004 Error - "Method'Range'of object'_Worksheet' failed" I tried a couple things, but nothing works.
    I would appreciate any suggestions on how to resolve the issue. Thanks.

  2. #2
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Modify VBA to work in a worksheet module

    Please Login or Register  to view this content.
    You would need to qualify both the Cells and Range highlighted in red to identify which sheet they apply to. I assume it would be Sheets("Combined"). The code would work in the standard module if Sheets("Combined") was the active sheet at runtime. But if you had a different sheet active, it would most likely error as well. Any time the compiler cannot figure which parent a range reference belongs to, it will give that error.
    Last edited by JLGWhiz; 02-25-2018 at 12:40 PM.
    Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
    Just when I think I am smart, I learn something new!

+ 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. [SOLVED] Need someone to edit Modify Macro's to work on entire work book
    By MarkKil in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-24-2018, 01:01 AM
  2. [SOLVED] Extra Sheet not a Chart or Old Macro Module or Worksheet - Appears to be normal module???
    By Doc.AElstein in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 06-06-2017, 04:10 AM
  3. I need to modify this Module: add date in G and time in H when F changes
    By raja1988 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-01-2013, 07:22 PM
  4. Modify Last_Cell_Value Module
    By JustinL in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-08-2007, 12:19 PM
  5. Modify a module to insert a column
    By iwilkin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-18-2005, 06:29 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