+ Reply to Thread
Results 1 to 14 of 14

Creating worksheets if not exist, copying from raw data if exists

  1. #1
    Forum Contributor
    Join Date
    05-13-2019
    Location
    Halifax, Canada
    MS-Off Ver
    2016
    Posts
    176

    Creating worksheets if not exist, copying from raw data if exists

    Hi experts,

    I am trying to create worksheet by using the following 2 codes alternatively but they are giving me the same 'Run-time error 1004' and creating the duplicate worksheets (Purpose-Processed(1)/(2) ....) every time I run macro.

    The codes are:

    Code 1:
    Please Login or Register  to view this content.
    Code 2:
    Please Login or Register  to view this content.
    I am not able to figure out what is wrong with these codes. The interesting thing is this same code is working in some other excel workbook with no error.

    Can anybody help me understand the reason for this error?

    Appreciate your response.

    Thanks
    Roshan Shakya
    Last edited by Roshan.Shakya; 07-19-2019 at 09:57 PM.

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Creating worksheets if not exist, copying from raw data if exists

    1)

    Please Login or Register  to view this content.
    should be
    Please Login or Register  to view this content.
    2) a typo
    Please Login or Register  to view this content.
    should be
    Please Login or Register  to view this content.

  3. #3
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: Creating worksheets if not exist, copying from raw data if exists

    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    05-13-2019
    Location
    Halifax, Canada
    MS-Off Ver
    2016
    Posts
    176

    Re: Creating worksheets if not exist, copying from raw data if exists

    Hi jindon,

    After amending the first code, I received run-time Error 13 (Type Mismatch) and did not create any duplicate, and after amending the second code, it created the duplicate of Purpose-Original and renamed it to Purpose-Processed for the first time, but the second time I run the code it gave me run-time Error 91 (object variable or with block variable not set).

    My objective is to create a duplicate worksheet (from Purpose-Original) for the first-time and rename it to Purpose-Processed. If the worksheet of name Purpose-Processed if already exist in the workbook then just delete all the data in that worksheet (Purpose-Processed) and copy data from Purpose-Original and paste that in Purpose-Processed.

    Thanks
    Roshan Shakya

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Creating worksheets if not exist, copying from raw data if exists

    OOps..

    It should be
    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    05-13-2019
    Location
    Halifax, Canada
    MS-Off Ver
    2016
    Posts
    176

    Re: Creating worksheets if not exist, copying from raw data if exists

    Hi jolivanes,

    It worked perfectly fine, but I want to put that exactly before Purpose-Original. I modified "After" with "before" in your code, but still it created after Purpose-Original.

    Thank you so much for your suggestion.

  7. #7
    Forum Contributor
    Join Date
    05-13-2019
    Location
    Halifax, Canada
    MS-Off Ver
    2016
    Posts
    176

    Re: Creating worksheets if not exist, copying from raw data if exists

    still a run-time error 13 (Type mismatch).

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Creating worksheets if not exist, copying from raw data if exists

    No idea why.
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    05-13-2019
    Location
    Halifax, Canada
    MS-Off Ver
    2016
    Posts
    176

    Re: Creating worksheets if not exist, copying from raw data if exists

    Your sample worked perfectly fine, but the same code here is giving me that error.

    appreciate your prompt response.

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Creating worksheets if not exist, copying from raw data if exists

    No way to solve, unless you upload your workbook.

  11. #11
    Forum Contributor
    Join Date
    05-13-2019
    Location
    Halifax, Canada
    MS-Off Ver
    2016
    Posts
    176

    Re: Creating worksheets if not exist, copying from raw data if exists

    " ' " and " ) " these were missed in my code resulting to type mismatch error.

    "If Not Evaluate("isref('Purpose-Processed!A1)") Then
    My code
    Please Login or Register  to view this content.
    Correct code:
    Please Login or Register  to view this content.
    Sorry to misunderstand you but I had appreciated for your earlier prompt response and not to appreciate seeking your prompt response. Thank you so much for your code.
    Last edited by Roshan.Shakya; 07-20-2019 at 06:13 AM.

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Creating worksheets if not exist, copying from raw data if exists

    That's OK and thanks for the rep.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  13. #13
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: Creating worksheets if not exist, copying from raw data if exists

    It's all sorted and you've got it working so that's all that counts.

    Re: but I want to put that exactly before Purpose-Original.
    How do you do this if you're creating a sheet named "Purpose-Original"?

  14. #14
    Forum Contributor
    Join Date
    05-13-2019
    Location
    Halifax, Canada
    MS-Off Ver
    2016
    Posts
    176

    Re: Creating worksheets if not exist, copying from raw data if exists

    Yeah its sorted, but its good to learn different methods to get the same result.

    I changed "Purpose-Original" that was mentioned in the code you've suggested by "Purpose-Processed and changed the following codes:

    Please Login or Register  to view this content.
    by
    Please Login or Register  to view this content.
    It worked. Thank you for your guidance jolivanes.

+ 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: 10
    Last Post: 05-19-2017, 09:23 PM
  2. [SOLVED] Creating New Worksheets, Copying Data to New Sheet Based on Matching Column Header
    By cbauer in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-08-2017, 03:21 AM
  3. [SOLVED] Return one value if data exists, another if data doesn't exist
    By dawsonsoo in forum Excel General
    Replies: 8
    Last Post: 01-20-2015, 08:49 AM
  4. [SOLVED] check if worksheet already exists when auto creating worksheets form a list
    By y0rk1e72 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-06-2012, 04:53 PM
  5. copying worksheets from one workbook to another and overwriting if they exist already
    By tinkerbelle in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-19-2010, 08:55 AM
  6. Replies: 4
    Last Post: 06-18-2006, 01:10 PM
  7. [SOLVED] Copying data & creating new worksheets - error
    By Jen in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-04-2006, 08:40 AM

Tags for this Thread

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