+ Reply to Thread
Results 1 to 7 of 7

Copying name supplied in userform to new sheet

  1. #1
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

    Copying name supplied in userform to new sheet

    Hello all. Can someone please help me save rows to the variable name stored in dtselect4 (which captures the name input by the user on a userform)?

    The sheet is created here:

    Please Login or Register  to view this content.
    and then the desired rows should be copied to the new sheet here:

    Please Login or Register  to view this content.
    butit isn't working. If I use a predefined sheet name, everything works fine.

    Complete code is here:

    Please Login or Register  to view this content.
    Thanks in advance,
    Andrew
    Last edited by drewship; 05-17-2009 at 06:52 PM.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,255

    Re: Copying name supplied in userform to new sheet

    Hello Andrew,

    Your post is a bit muddled. Is dtselect4 the name of the TextBox control on the VBA UserForm? I ask because your title seems to indicate this but in your code you assign this as a Worksheet object.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

    Re: Copying name supplied in userform to new sheet

    Hello Leith.

    The userform code is below:


    Please Login or Register  to view this content.
    As you can see, txtInput4.Text is used to collect and store the input for the desired sheet name. Then dtselect4 assumes this value which is then used to name a new sheet using the previous code. I don't know if it should be a worksheet object but I am still trying to learn VB. I tried several variations before I posted my question so what you see is my last attempt at figuring it out myself.

    Basically, I want a new sheet to be created using the name input on the user form. Then I need all the rows that are within the date range to be copied to the new sheet. The only thing that is not working is copying the rows. I am sure it is a small variation using dtselect4 but I am stumped.

    Can you help?

    Thanks,
    Andrew

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,255

    Re: Copying name supplied in userform to new sheet

    Hello Andrew,

    To access the TextBox from Excel, you need to qualify it with the UserForm name. You can assign your new worksheet to an object variable and then set the name property to the TextBox's contents. The changes are in blue.
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

    Re: Copying name supplied in userform to new sheet

    Hello Leith.

    I have uploaded the spreadsheet for your reference. If you click the button on the 'Utilities' tab, you will enter the start date (I have been using the 7th), end date (not changed), and the name of the new sheet. I tried changing the code as you suggested but I get a 'subscript out of range' at line:

    Please Login or Register  to view this content.
    If I change it to :

    Please Login or Register  to view this content.
    which is a sheet I created just to test this code, 2 things happen. The first is that the header is saved in a new sheet created from the userform input, and the 2nd is that all matching rows are saved in StaticSheet. I am still trying different code combinations with no success. Both the header and the rows matching the date range need to be saved to a new sheet created from the userform.

    Not sure what I am missing but hopefully you can work your magic now that you have the spreadsheet.

    Thanks,
    Andrew
    Attached Files Attached Files
    Last edited by drewship; 05-15-2009 at 09:56 PM. Reason: updated file

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,255

    Re: Copying name supplied in userform to new sheet

    Hello Andrew,

    The code fails because of illegal characters in the Worksheet name. Here are the 7 characters you can not use in worksheet name...
    ? / \ : [ ] *

    You need to change your code to format the date using hyphens. Make these changes and the sheet will be added successfully.
    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

    Re: Copying name supplied in userform to new sheet

    Thanks Leith!!!
    I thought it was probably something simple. I have run a couple of tests and it seems to be working. I will be out of town until tomorrow and I will try to finish testing and then mark the thread as resolved.

    Andrew

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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