+ Reply to Thread
Results 1 to 6 of 6

Is there a way to get vba of a worksheet already formatted and used?

  1. #1
    Forum Contributor
    Join Date
    08-22-2009
    Location
    Manitoba
    MS-Off Ver
    Office 2010
    Posts
    524

    Is there a way to get vba of a worksheet already formatted and used?

    Title basically says what I am wondering and I am pretty sure of the answer. No. Why I am asking is I have worksheets with much formatting and formulas and conditional formatting that it will take a while to record a macro to duplicate it and then it still probably wouldn't be 100%.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Is there a way to get vba of a worksheet already formatted and used?

    Delete the data only, not the formulas and formatting and save the sheet as a template file (xltx) and set the file attributes as read only. Then when the file is used, Excel will force the user to rename it in order to save it - the template will remain unchanged for future use.
    Ben Van Johnson

  3. #3
    Forum Contributor
    Join Date
    08-22-2009
    Location
    Manitoba
    MS-Off Ver
    Office 2010
    Posts
    524

    Re: Is there a way to get vba of a worksheet already formatted and used?

    Ok, but how do I get the vba code from that worksheet in order to be able to create another when I do not have any vba code for the original?

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Is there a way to get vba of a worksheet already formatted and used?

    I'm confused, you did not mention VBA code in the original post. If the original does have VBA code, then saving it as xltx will delete the code. If the workbook does have macros, and you do want them available in the new template, then save as xltm

  5. #5
    Forum Contributor
    Join Date
    08-22-2009
    Location
    Manitoba
    MS-Off Ver
    Office 2010
    Posts
    524

    Re: Is there a way to get vba of a worksheet already formatted and used?

    Hmm, how do I describe this better. There is no vba, macro or sub for this worksheet. I want to make a duplicate of it. Yes, I can just make a copy, but I want to make a copy of it using vba. So, about the only way I know how, it to start from scratch and either record the steps or write out each step. From my limited knowledge of vba and what I have researched I am quite sure this is the only way. Sorry, I don't know how better to explain my question.

  6. #6
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Is there a way to get vba of a worksheet already formatted and used?

    A macro to copy the original sheet to a place in the same workbook looks like:
    Please Login or Register  to view this content.
    But it duplicates the data in the original as well as formulas and formatting.
    Suggestion:
    1. Delete the user data on the original sheet and rename it, say, "Original"
    2. Copy the macro below with appropriate changes:
    Please Login or Register  to view this content.
    3. Add a button on the sheet and assign the macro copy2

    The code will copy the original sheet to the end of the workbook and delete the button.

+ 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. Conditional Output to Pre-Formatted Worksheet
    By sisu in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-14-2011, 12:05 PM
  2. Insert a formatted worksheet into a workbook
    By Cled in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 08-13-2009, 09:55 AM
  3. [SOLVED] Across a worksheet, which cells I have formatted as locked?
    By ZM in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-27-2005, 09:05 AM
  4. [SOLVED] Create new formatted row in another worksheet
    By Warren in forum Excel General
    Replies: 0
    Last Post: 08-23-2005, 12:05 AM
  5. Formatted cells on summary worksheet
    By Benfanfromlo in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-14-2005, 01:06 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