+ Reply to Thread
Results 1 to 7 of 7

Saving a sheet values only as a new workbook

  1. #1
    Forum Contributor
    Join Date
    03-08-2011
    Location
    Lubbock, TX, USA
    MS-Off Ver
    MS OFFICE 365 EXCEL, OUTLOOK, WORD, POWERPOINT
    Posts
    194

    Saving a sheet values only as a new workbook

    Hello Everyone. I have a macro that saves a specific sheet from the master workbook into it's own workbook. It's nothing but raw data that we can use as a snapshot of a moment in time weeks down the road. No formatting or anything. Here is the VBA code.

    Please Login or Register  to view this content.
    I need to modify it. Currently another macro copies and pastes a lot of data to the backfill sheet so it's all in one place to save as it's own workbook. I changed the sheet so that formulas directly pull the data without having to go thru the whole select, copy, paste values etc. The data is simply there. So I ran the macro and it created a backfill file but it retained the formulas. I was wondering if up in the code where it says
    Please Login or Register  to view this content.
    can it be modified so that it only saves the values?

    I can make it work by opening the new file and just copying and pasting over itself with values but I'd rather not.

    Thanks for looking and any and all insight appreciated. And, if an obvious answer isn't available I will be happy to take pointers on where to go and study more.

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Saving a sheet values only as a new workbook


    Hi,

    after the Copy codeline just try With ActiveSheet: .Formula = .Value2: End With

  3. #3
    Forum Contributor
    Join Date
    03-08-2011
    Location
    Lubbock, TX, USA
    MS-Off Ver
    MS OFFICE 365 EXCEL, OUTLOOK, WORD, POWERPOINT
    Posts
    194

    Re: Saving a sheet values only as a new workbook

    I appreciate your time Marc. It didn't work for me thuogh. I got error 438 Object doesn't support this property or method.
    Did I get it right? There are times I misunderstand simple directions.

    Please Login or Register  to view this content.

  4. #4
    Forum Expert
    Join Date
    10-11-2021
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    1,367

    Re: Saving a sheet values only as a new workbook

    Try this

    Please Login or Register  to view this content.
    Last edited by JEC.; 03-21-2022 at 05:30 PM.

  5. #5
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Saving a sheet values only as a new workbook


    Quote Originally Posted by Mr_Phil View Post
    It didn't work for me thuogh.
    Yes my bad obviously as it must be With ActiveSheet.UsedRange … It's what happens when answering under a phone call !

  6. #6
    Forum Contributor
    Join Date
    03-08-2011
    Location
    Lubbock, TX, USA
    MS-Off Ver
    MS OFFICE 365 EXCEL, OUTLOOK, WORD, POWERPOINT
    Posts
    194

    Re: Saving a sheet values only as a new workbook

    That did the trick JEC. Thank you very much.

  7. #7
    Forum Contributor
    Join Date
    03-08-2011
    Location
    Lubbock, TX, USA
    MS-Off Ver
    MS OFFICE 365 EXCEL, OUTLOOK, WORD, POWERPOINT
    Posts
    194

    Re: Saving a sheet values only as a new workbook

    I appreciate it though Marc. I learned a bit more. thanks again.

+ 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. Saving sheet into a new workbook
    By luispebm in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-03-2015, 06:50 PM
  2. Exporting each sheet to a new workbook and saving workbook in new folder with timestamp
    By amphinomos in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-19-2013, 11:19 AM
  3. Saving One Sheet In New Workbook
    By Storm08 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-26-2012, 08:12 AM
  4. [SOLVED] saving a sheet in a new workbook
    By steve in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-29-2005, 12:25 PM
  5. Saving a sheet in a workbook as .csv but not changing workbook name
    By gloryofbach in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-30-2005, 05:05 PM
  6. Saving one sheet within a workbook
    By David Vollmer in forum Excel General
    Replies: 2
    Last Post: 09-14-2005, 01:05 PM
  7. saving sheet as new workbook
    By Nigel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-11-2005, 10:05 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