Copying name supplied in userform to new sheet

**drewship** · 05-15-2009, 02:58 PM

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

**Leith Ross** · 05-15-2009, 05:10 PM

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.

**drewship** · 05-15-2009, 06:00 PM

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

**Leith Ross** · 05-15-2009, 06:10 PM

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.

**drewship** · 05-15-2009, 09:50 PM

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

**Leith Ross** · 05-15-2009, 11:04 PM

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.

**drewship** · 05-16-2009, 07:17 AM

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