+ Reply to Thread
Results 1 to 4 of 4

Worksheet count and reference issue

  1. #1
    Forum Contributor
    Join Date
    09-23-2007
    Location
    Melbourne, Australia
    Posts
    173

    Worksheet count and reference issue

    Hi
    Within the same sub procedure I've gone through the steps below:

    1 - I've started with two sheets, different data in each and the first step is to filter the first sheet for certain values, inserting a new worksheet and naming it and pasting the filtered values into the new sheet. Then deleting the first worksheet. (no probs, works great on its own)

    2 - I have then gone through this process again with the second worksheet, buy filtering, inserting and renaming a new worksheet and pasting the filtered values into the new worksheet, then deleting the filtered worksheet (this is the second worksheet). (this also works fine on its own).

    3 -I originally had two separate sub procedures to separate the two tasks above though when I joined them together and ran the macro the first part works fine ( Number 1 above ) though when it comes to filtering the second worksheet it goes back and filteres the worksheet I inserted the filtered values in from step 1 above.

    I have calculation set to manual and think this might be it. Is this why the procedure goes back to the first worksheet used in 1 and filters values in that instead of the second sheet.

    My other theory is that when I insert a new worksheet, it is identified as the second new worksheet I inserted (even though I have renamed them both before performing the actions above). Excel may not recognise them as different sheets?

    Does anyone know what I may have done wrong there. The macro is too long ot paste in though I think this may be a conceptual issue involvind how vba identifies the sheets. If anyone could shead some light on this, it would be much apprecieated. Thanks.

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,153
    You probably referencing the Worksheet by ActiveWorksheet.

    Change it to specific sheet names like
    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    09-23-2007
    Location
    Melbourne, Australia
    Posts
    173

    I named the worksheets

    Hi
    Thanks for coming back though I have named the worksheet. The fact that steps 1 & 2 are in the same sub procedure is causing the issue. Would haveing calculation set to manual instead of auto affect this?

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,153
    Would haveing calculation set to manual instead of auto affect this?
    I don't think so.
    Do you want to post your code?

+ Reply to Thread

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