Results 1 to 2 of 2

Obect Required error when variable already set earlier in sub?

Threaded View

  1. #1
    Forum Contributor
    Join Date
    05-12-2014
    MS-Off Ver
    Excel 2013
    Posts
    131

    Obect Required error when variable already set earlier in sub?

    Hey folks! I have what I hope is an easy question to answer.

    So I have code where I'm setting the following variables:
    Set x = Workbooks.Open("S:\" & su & pe & "\" & ft1 & "\WPS.xlsm")
    Set y = Workbooks.Open("S:\" & su & pe & "\" & pe & "\MasterWPS.xlsm")
    Set z = x.Sheets(mo)
    Set w1 = z.Range("A3:H12")
    Set w2 = z.Range("A19:H28")
    Set w3 = z.Range("A35:H44")
    Set w4 = z.Range("A51:H60")
    Set w5 = z.Range("A67:H76")
    For the first few lines, whenever I call on w1.Copy, it works great.

    However, if I have code later in the sub that looks like this:
    Set x2 = Workbooks.Open("S:\" & su & pe & "\" & ft2 & "\WPS.xlsm")
    Set y = Workbooks.Open("S:\" & su & pe & "\" & pe & "\MasterWPS.xlsm")
    Set z = x2.Sheets(mo)
    I get an Object Required error in the debugger. Do I have to set w1-w5 every time even though the range never changes? I'm thinking it has something to do with the fact that I'm re-setting z to refer to a new worksheet, but I figured since I'm re-setting z I wouldn't need to reset the w's as well since those just call on the most recent z variable.

    But I'm also learning VBA as I go, so I know I'm using flawed logic here (if for no other reason than it isn't working :P). Is anyone able to let me know where I'm going wrong here, whether it be syntax or thinking?

    Thanks!
    Last edited by mrbusto71; 08-29-2016 at 07:18 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 7
    Last Post: 05-19-2016, 05:12 AM
  2. [SOLVED] Setting a range variable to its offset triggers an "Object required" error
    By Durarara in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-27-2016, 01:50 PM
  3. [SOLVED] New Error/Old Code - Run Time Error 91; Object variable or With block variable not set
    By humboldtguy in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-15-2014, 12:39 AM
  4. Set Variable = File Path generates "Object Required" Error
    By Brian48072 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-30-2013, 11:42 AM
  5. Unable to delete sheet due to macros copied earlier - error 400
    By talhawahab in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-12-2012, 11:52 PM
  6. Variable Required Error
    By Baybars in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-26-2010, 07:37 PM
  7. Error. Variable Required
    By shapper in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-19-2008, 03:36 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