+ Reply to Thread
Results 1 to 14 of 14

Save as XLSX based on cell value

  1. #1
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Poinciana, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    337

    Save as XLSX based on cell value

    Hi all! I have a workbook (xlsm form), that is saved (as xlsx format) and distributed. The file name is auto-generated based on cells on a specific worksheet "Main". This sheet needs to be deleted, and then there are 3 sheets hidden (2 of which are extra hidden). I modified a previous macro for these needs, but ran into an error on the way. The error I get is a compile error (see screenshot). Now I am confused as I have used this snippet of code before without any issues. Attached is the code for viewing and my sample workbook.

    In a nutshell:

    sheet7 ("Main")
    is the Control Panel, and deleted, this sheet is the leftmost tab
    Sheet1 ("data")
    hidden
    Sheet8 ("calcs")
    extra hidden
    Sheet3 ("codes")
    extra hidden
    Please Login or Register  to view this content.
    Attached Images Attached Images
    Attached Files Attached Files
    ~*~ Sherry ~*~
    Poinciana, FL

  2. #2
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,103

    Re: Save as XLSX based on cell value

    See if this makes a difference, added With....End With

    Please Login or Register  to view this content.

  3. #3
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Poinciana, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    337

    Re: Save as XLSX based on cell value

    maniacb,

    Thanks... I just tried your potential solution. But it did not work. I got Run-time error 424 (screenshot below), and when I went to click debug it shows the portion of the code where "Main" is deleted (screenshot below). Which I noticed is BEFORE the part of the code that you changed to include the With/End With. I think I am more confused now.
    Attached Images Attached Images

  4. #4
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,103

    Re: Save as XLSX based on cell value

    I think their is missing a SaveAs command line, something like:

    Please Login or Register  to view this content.
    How does the code otherwise know that a new workbook was created?

  5. #5
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Poinciana, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    337

    Re: Save as XLSX based on cell value

    maniacb,

    Nope, I am still getting that exact same Run-time 424 error. And as before debug references the delete portion of the code for the "Main" tab.

  6. #6
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Poinciana, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    337

    Re: Save as XLSX based on cell value

    maniacb,
    Any other ideas? Still encountering that Runt-time 424 error

  7. #7
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,103

    Re: Save as XLSX based on cell value

    I used your code and it works for me, when I don’t use the delete line. How about changing the sheet name from 1 to the actual name on the sheet?

  8. #8
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,103

    Re: Save as XLSX based on cell value

    The code should look like

    Please Login or Register  to view this content.

  9. #9
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Poinciana, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    337

    Re: Save as XLSX based on cell value

    Maniacb,

    I am not sure what I did, but I managed to mess this up somehow. I am still getting the same 424 object required error. Here is my current code as it has been modified along the way.

    Please Login or Register  to view this content.
    Attached Images Attached Images

  10. #10
    Registered User
    Join Date
    02-05-2020
    Location
    Nairobi, Kenya
    MS-Off Ver
    Office 365
    Posts
    14

    Re: Save as XLSX based on cell value

    first you have typed wbNew instead of wbkNew
    secondly you have not initiated (set) the wbkNew object
    third the quotation marks in the main should be ("Main") and not (“Main”)

  11. #11
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,103

    Re: Save as XLSX based on cell value

    t.gatheru had some great suggestion. I updated the code accordingly and rearranged it so that it now does what you expect. Give it a try.

    Please Login or Register  to view this content.

  12. #12
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Poinciana, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    337

    Re: Save as XLSX based on cell value

    Quote Originally Posted by t.gatheru View Post
    first you have typed wbNew instead of wbkNew
    secondly you have not initiated (set) the wbkNew object
    third the quotation marks in the main should be ("Main") and not (“Main”)
    @t.gatheru,

    Thanks for pointing out those items. I claim "blonde" moment! LOL My schedule has been crazy, as we are in the middle of a move.... I am working 7am-7pm, and packing during any awake hours! Obviously with that going on, it is easy for me as a newbie to miss these kind of things. Thanks so very much. Now, and in the future I understand what to look for!

  13. #13
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Poinciana, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    337

    Re: Save as XLSX based on cell value

    @mainicb,

    Thanks for updating the code. I updated the code and now I am confused. I tried it on my sample workbook which I supplied, which was blank, and the code works flawless. But then I tried on a copy of my live workbook that contains actual data, and I get this error. Now granted I can click on end, and save it manually, but obviously that defeats the purpose. Any clue what would cause this?
    Attached Images Attached Images

  14. #14
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Poinciana, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    337

    Re: Save as XLSX based on cell value

    I am not sure exactly what the problem is... but I know I can blame One-Drive! Ever since the company forced us to use it, everything is on the cloud, no more standard C drive. I was under the impression it was about backing up to the cloud... anyway, as a result, the path (without my folder structure) is hyper long! I tried saving a copy of my live file into a "working folder I added to my download folder, and the the script was perfect and hyper fast. My issue is with One-drive. Thanks so much!!!!!!

+ 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] Save workbook as two separate formats (CSV and XLSX) and leave XLSX open
    By mc84excel in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-04-2019, 07:20 PM
  2. [SOLVED] Save sheet with 2 cell variables as xlsx file
    By Webbers in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 03-21-2019, 05:43 PM
  3. [SOLVED] Macro to copy 1 tab and save as xlsx based on multiple cell values
    By Webbers in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 06-22-2015, 09:20 AM
  4. cannot save csv as xls or xlsx
    By khhleung in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-06-2014, 09:41 PM
  5. [SOLVED] Save as .xlsx and .txt
    By MarcoNecroX in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-29-2013, 08:56 PM
  6. Replies: 1
    Last Post: 09-12-2013, 10:36 PM
  7. Replies: 6
    Last Post: 08-28-2012, 03:51 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