| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
STILL Having VBA Problems!!!!!!!
I posted something on here yesterday, and I got very good responses. However,
after I implemented each suggestion into VBA the macro worked only about 10 times. After that it came up with an error that said the worksheet could not be copied...or something like that. This is what my code for that section looks like... This is at the beginning of my macro...what I need to do is copy a worksheet. That's all I need to do...and it won't work...ANY SUGGESTIONS??? Sheets("201").Copy After:=Sheets(Sheets.Count) Sheets(Sheets.Count).Name = InputBox("Enter the number of the new distributor.") Thanks, Elise |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
STILL Having VBA Problems!!!!!!!
Sorry, I forgot to mention...I did not rename, delete, or otherwise move the
"201" Sheet... "Elise148" wrote: I posted something on here yesterday, and I got very good responses. However, after I implemented each suggestion into VBA the macro worked only about 10 times. After that it came up with an error that said the worksheet could not be copied...or something like that. This is what my code for that section looks like... This is at the beginning of my macro...what I need to do is copy a worksheet. That's all I need to do...and it won't work...ANY SUGGESTIONS??? Sheets("201").Copy After:=Sheets(Sheets.Count) Sheets(Sheets.Count).Name = InputBox("Enter the number of the new distributor.") Thanks, Elise |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
STILL Having VBA Problems!!!!!!!
Are you trying to re-use the same name? Invalid characters in the name?
Where is the error? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Elise148" wrote in message ... I posted something on here yesterday, and I got very good responses. However, after I implemented each suggestion into VBA the macro worked only about 10 times. After that it came up with an error that said the worksheet could not be copied...or something like that. This is what my code for that section looks like... This is at the beginning of my macro...what I need to do is copy a worksheet. That's all I need to do...and it won't work...ANY SUGGESTIONS??? Sheets("201").Copy After:=Sheets(Sheets.Count) Sheets(Sheets.Count).Name = InputBox("Enter the number of the new distributor.") Thanks, Elise |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
STILL Having VBA Problems!!!!!!!
Elise,
There was in earlier versions of Excel a problem with code names for sheets that happened after several copies. The code names got longer as you copied sheets. I don't recall which release had it, but I think it's been fixed. Take a look at the code names in the sheets in the VBE Project Explorer. If you see long strings of characters, I think that's it. Nowadays, the sheet's code names are Sheet1, Sheet2, etc. The code names are not accessible via VBA, and they aren't the same as the names we give them. If this is the case, you might need to make your copies from a master, and don't copy copies of copies, etc. Or maybe check the knowledge base at microsoft.com. -- Earl Kiosterud www.smokeylake.com Note: Top-posting has been the norm here. Some folks prefer bottom-posting. But if you bottom-post to a reply that's already top-posted, the thread gets messy. When in Rome... ----------------------------------------------------------------------- "Elise148" wrote in message ... I posted something on here yesterday, and I got very good responses. However, after I implemented each suggestion into VBA the macro worked only about 10 times. After that it came up with an error that said the worksheet could not be copied...or something like that. This is what my code for that section looks like... This is at the beginning of my macro...what I need to do is copy a worksheet. That's all I need to do...and it won't work...ANY SUGGESTIONS??? Sheets("201").Copy After:=Sheets(Sheets.Count) Sheets(Sheets.Count).Name = InputBox("Enter the number of the new distributor.") Thanks, Elise |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
STILL Having VBA Problems!!!!!!!
Elise,
That is a problem with the codename of the sheet becoming too long. Try Dim myName As String Dim mySht As Worksheet myName = InputBox("Enter the number of the new distributor.") Sheets("201").Copy After:=Sheets(Sheets.Count) Set mySht = Sheets(Sheets.Count) mySht.Name = myName mySht.Parent.VBProject.VBComponents(mySht.CodeName ) _ ..Properties("_CodeName") = "sht" & Application.Proper(Replace(myName, " ", "")) This will require a reference to the MS VBA extensibility. HTH, Bernie MS Excel MVP "Elise148" wrote in message ... I posted something on here yesterday, and I got very good responses. However, after I implemented each suggestion into VBA the macro worked only about 10 times. After that it came up with an error that said the worksheet could not be copied...or something like that. This is what my code for that section looks like... This is at the beginning of my macro...what I need to do is copy a worksheet. That's all I need to do...and it won't work...ANY SUGGESTIONS??? Sheets("201").Copy After:=Sheets(Sheets.Count) Sheets(Sheets.Count).Name = InputBox("Enter the number of the new distributor.") Thanks, Elise |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
STILL Having VBA Problems!!!!!!!
The error comes up before I can enter a name in the Input Box, and before
when it was working, I was entering differnet names - they were no worksheet names that were the same. No invalid characters either. The error lies in the first section of the code I listed below. It's a 1004 error. I looked up information about it, and then I added a save feature in the macro. I closed the workbook. Nothing - it still wouldn't work... "Bob Phillips" wrote: Are you trying to re-use the same name? Invalid characters in the name? Where is the error? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Elise148" wrote in message ... I posted something on here yesterday, and I got very good responses. However, after I implemented each suggestion into VBA the macro worked only about 10 times. After that it came up with an error that said the worksheet could not be copied...or something like that. This is what my code for that section looks like... This is at the beginning of my macro...what I need to do is copy a worksheet. That's all I need to do...and it won't work...ANY SUGGESTIONS??? Sheets("201").Copy After:=Sheets(Sheets.Count) Sheets(Sheets.Count).Name = InputBox("Enter the number of the new distributor.") Thanks, Elise |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
STILL Having VBA Problems!!!!!!!
I tried that, and it has worked for the last few times I've run it. Is there
something in the code below that brings up a box to connect to a link? I'm so confused because now it's bringing up a dialog box that tells the macro to draw a number from another workbook. It's never done this before...any ideas? Also, what does the "Dim" mean? "Bernie Deitrick" wrote: Elise, That is a problem with the codename of the sheet becoming too long. Try Dim myName As String Dim mySht As Worksheet myName = InputBox("Enter the number of the new distributor.") Sheets("201").Copy After:=Sheets(Sheets.Count) Set mySht = Sheets(Sheets.Count) mySht.Name = myName mySht.Parent.VBProject.VBComponents(mySht.CodeName ) _ ..Properties("_CodeName") = "sht" & Application.Proper(Replace(myName, " ", "")) This will require a reference to the MS VBA extensibility. HTH, Bernie MS Excel MVP "Elise148" wrote in message ... I posted something on here yesterday, and I got very good responses. However, after I implemented each suggestion into VBA the macro worked only about 10 times. After that it came up with an error that said the worksheet could not be copied...or something like that. This is what my code for that section looks like... This is at the beginning of my macro...what I need to do is copy a worksheet. That's all I need to do...and it won't work...ANY SUGGESTIONS??? Sheets("201").Copy After:=Sheets(Sheets.Count) Sheets(Sheets.Count).Name = InputBox("Enter the number of the new distributor.") Thanks, Elise |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
STILL Having VBA Problems!!!!!!!
Elise,
Is there something in the code below that brings up a box to connect to a link? I'm so confused because now it's bringing up a dialog box that tells the macro to draw a number from another workbook. It's never done this before...any ideas? It should just ask for the new number - as a string, no link. Worked for me each time. Also, what does the "Dim" mean? Dim is a dimensioning statement, which tells the VBA what type of variable you want to use. In this case, it is saying that myName should be a string, which prevents some autoconversion from happening when you type a number into the inputbox. HTH, Bernie MS Excel MVP "Bernie Deitrick" wrote: Elise, That is a problem with the codename of the sheet becoming too long. Try Dim myName As String Dim mySht As Worksheet myName = InputBox("Enter the number of the new distributor.") Sheets("201").Copy After:=Sheets(Sheets.Count) Set mySht = Sheets(Sheets.Count) mySht.Name = myName mySht.Parent.VBProject.VBComponents(mySht.CodeName ) _ ..Properties("_CodeName") = "sht" & Application.Proper(Replace(myName, " ", "")) This will require a reference to the MS VBA extensibility. HTH, Bernie MS Excel MVP "Elise148" wrote in message ... I posted something on here yesterday, and I got very good responses. However, after I implemented each suggestion into VBA the macro worked only about 10 times. After that it came up with an error that said the worksheet could not be copied...or something like that. This is what my code for that section looks like... This is at the beginning of my macro...what I need to do is copy a worksheet. That's all I need to do...and it won't work...ANY SUGGESTIONS??? Sheets("201").Copy After:=Sheets(Sheets.Count) Sheets(Sheets.Count).Name = InputBox("Enter the number of the new distributor.") Thanks, Elise |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
STILL Having VBA Problems!!!!!!!
Maybe a long-shot, but try this in the loop
Application.VBE.CommandBars.FindControl(ID:=578).E xecute -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Elise148" wrote in message ... The error comes up before I can enter a name in the Input Box, and before when it was working, I was entering differnet names - they were no worksheet names that were the same. No invalid characters either. The error lies in the first section of the code I listed below. It's a 1004 error. I looked up information about it, and then I added a save feature in the macro. I closed the workbook. Nothing - it still wouldn't work... "Bob Phillips" wrote: Are you trying to re-use the same name? Invalid characters in the name? Where is the error? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Elise148" wrote in message ... I posted something on here yesterday, and I got very good responses. However, after I implemented each suggestion into VBA the macro worked only about 10 times. After that it came up with an error that said the worksheet could not be copied...or something like that. This is what my code for that section looks like... This is at the beginning of my macro...what I need to do is copy a worksheet. That's all I need to do...and it won't work...ANY SUGGESTIONS??? Sheets("201").Copy After:=Sheets(Sheets.Count) Sheets(Sheets.Count).Name = InputBox("Enter the number of the new distributor.") Thanks, Elise |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
STILL Having VBA Problems!!!!!!!
Okay, thank you!
"Bernie Deitrick" wrote: Elise, Is there something in the code below that brings up a box to connect to a link? I'm so confused because now it's bringing up a dialog box that tells the macro to draw a number from another workbook. It's never done this before...any ideas? It should just ask for the new number - as a string, no link. Worked for me each time. Also, what does the "Dim" mean? Dim is a dimensioning statement, which tells the VBA what type of variable you want to use. In this case, it is saying that myName should be a string, which prevents some autoconversion from happening when you type a number into the inputbox. HTH, Bernie MS Excel MVP "Bernie Deitrick" wrote: Elise, That is a problem with the codename of the sheet becoming too long. Try Dim myName As String Dim mySht As Worksheet myName = InputBox("Enter the number of the new distributor.") Sheets("201").Copy After:=Sheets(Sheets.Count) Set mySht = Sheets(Sheets.Count) mySht.Name = myName mySht.Parent.VBProject.VBComponents(mySht.CodeName ) _ ..Properties("_CodeName") = "sht" & Application.Proper(Replace(myName, " ", "")) This will require a reference to the MS VBA extensibility. HTH, Bernie MS Excel MVP "Elise148" wrote in message ... I posted something on here yesterday, and I got very good responses. However, after I implemented each suggestion into VBA the macro worked only about 10 times. After that it came up with an error that said the worksheet could not be copied...or something like that. This is what my code for that section looks like... This is at the beginning of my macro...what I need to do is copy a worksheet. That's all I need to do...and it won't work...ANY SUGGESTIONS??? Sheets("201").Copy After:=Sheets(Sheets.Count) Sheets(Sheets.Count).Name = InputBox("Enter the number of the new distributor.") Thanks, Elise |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| More zip + 4 problems | Excel Discussion (Misc queries) | |||
| Tab key problems | Excel Discussion (Misc queries) | |||
| OLE Problems | Excel Worksheet Functions | |||
| problems with? I don't know | Excel Discussion (Misc queries) | |||
| If then problems | Excel Discussion (Misc queries) | |||
Linear Mode
