+ Reply to Thread
Results 1 to 6 of 6

Macro for automatically creating a sheet with template

  1. #1
    Registered User
    Join Date
    07-20-2018
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    8

    Macro for automatically creating a sheet with template

    I have created a macro for the automatic creation of a sheet when I fill in a cell, which also uses a certain template. The macro is as follows:

    Please Login or Register  to view this content.
    The macro works whenever I copy and paste an X amount of cells, but does not work when I fill in a single cell. I don't see the problem, but VBA tells me the problem is in .Name = cel.Value. Can anyone help me solve this problem? Many thanks in advance.

    Luuk

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Macro for automatically creating a sheet with template

    Probably easier to diagnose the problem if you could attach the workbook.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

    BSB

  3. #3
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Macro for automatically creating a sheet with template

    Having had time to think about it, I assume you mean when you manually enter a value into a cell in column A rather than paste something in.
    The problem there is this line:
    Please Login or Register  to view this content.
    When you enter a value in a cell then hit enter to confirm it, the selection moves to another cell (either below or to the right dependent on your setup) so your variable selectedRange is set to an empty cell and this causes issues when you're trying to name a sheet as you cannot give a sheet no name.

    You can test this by entering a value in a single cell and confirming it with Ctrl+Enter so the cell selection doesn't change.

    Your code would also benefit from some error handling. If the user changes a cell value to nothing by deleting the contents of a cell it will also throw up an error.

    BSB
    Last edited by BadlySpelledBuoy; 07-20-2018 at 04:45 AM.

  4. #4
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Macro for automatically creating a sheet with template

    Try this slightly amended version of your code:
    Please Login or Register  to view this content.
    BSB

  5. #5
    Registered User
    Join Date
    07-20-2018
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    8

    Re: Macro for automatically creating a sheet with template

    That did the trick! Thanks a lot!!. Have been working on this issue for hours. Cheers!

  6. #6
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Macro for automatically creating a sheet with template

    No problem. Happy to help

    BSB

+ 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. Creating a Rule to automatically save received emails as Template files
    By Speshul in forum Outlook Formatting & Functions
    Replies: 1
    Last Post: 11-23-2015, 10:44 AM
  2. Creating a Template that posts data to another Sheet
    By dana.dobrow in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-14-2014, 01:56 PM
  3. creating spreadsheets using a template and data sheet
    By anthonette in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-07-2014, 11:11 AM
  4. Creating New sheet using template
    By sridhar_shri03 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-15-2013, 08:37 AM
  5. Creating a Macro that will automatically pull data from a new spreed sheet.
    By btm88 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-25-2013, 05:23 PM
  6. VBA - Creating a new named sheet with a sheet template
    By Glensafro in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-09-2012, 06:49 AM
  7. Copy values to template & creating new sheet
    By riceordie in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-29-2007, 03:11 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