Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
The differents of the sheets name and caption.
How do i paste the cellvalue in sheet2 range("C2") from
sheet("Ark1)range ("I14"). The user of the workbook has given sheet2 a new name when this macro i running. So I has to use the "Real" name of the sheet. The normal copy and paste only work with the "caption" of the sheet. the ("Ark1")sheet is beeing deleted after, so I can't use a hyperlink. The problem is at line 08-09. Does sombody have a suggestion to my challenge please! 01. Private Sub CommandButton3_Click() 02. Dim c As range 03. Set c = ActiveCell 04. range("I14").Select 05. If IsEmpty(c) Then GoTo line2 Else GoTo line1 06. line1: 07. Sheet2.Visible = True 08. Sheet2.range("C2") = Worksheets("Ark1").range _ 09. ("I14").Value 10. Sheet2.Name = Worksheets("Ark1").range("N14").Value 11. Line2: 12. 13. 'same thing here 14. End sub Mvh Aksel *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
The differents of the sheets name and caption.
There are two "names" for the sheet.
There's the name that you and the user see in the worksheet tab. And there's a codename that you as a developer can see in the properties window inside the VBE when you have that worksheet selected in the project explorer. Hit alt-f11 to get to the VBE hit ctrl-r to see the project explorer (much like windows explorer) Select your worksheet in your project hit F4 to see the properties window You'll see a (name) property at the top. This is the Codename for that sheet. You'll also see a Name (without the parens) -- that's the name on the worksheet tab. You can use the codename in your code to avoid problems with the user changing the worksheet name on the tab. But it looks like you did when you used sheet2.range("C2") in your sample code. If you were using the Name (on the tab), your code would have looked like: worksheets("sheet2").range("C2") Axel wrote: How do i paste the cellvalue in sheet2 range("C2") from sheet("Ark1)range ("I14"). The user of the workbook has given sheet2 a new name when this macro i running. So I has to use the "Real" name of the sheet. The normal copy and paste only work with the "caption" of the sheet. the ("Ark1")sheet is beeing deleted after, so I can't use a hyperlink. The problem is at line 08-09. Does sombody have a suggestion to my challenge please! 01. Private Sub CommandButton3_Click() 02. Dim c As range 03. Set c = ActiveCell 04. range("I14").Select 05. If IsEmpty(c) Then GoTo line2 Else GoTo line1 06. line1: 07. Sheet2.Visible = True 08. Sheet2.range("C2") = Worksheets("Ark1").range _ 09. ("I14").Value 10. Sheet2.Name = Worksheets("Ark1").range("N14").Value 11. Line2: 12. 13. 'same thing here 14. End sub Mvh Aksel *** Sent via Developersdex http://www.developersdex.com *** -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
The differents of the sheets name and caption.
Thank you for reply! I now about the "two names". My problem is that I have always used the tab name when I make a macro. but in this case I have to use the codename, but then the program has to be written different. *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
The differents of the sheets name and caption.
Yep. Your code will have to change.
And didn't you already start using the code names with line 7 and half of line 8? 07. Sheet2.Visible = True 08. Sheet2.range("C2") = .... So why not just rewrite the stuff you need to rewrite? Axel wrote: Thank you for reply! I now about the "two names". My problem is that I have always used the tab name when I make a macro. but in this case I have to use the codename, but then the program has to be written different. *** Sent via Developersdex http://www.developersdex.com *** -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
The differents of the sheets name and caption.
Thanks again for answer. I did not use the codename with ("Ark1"), because now I am using a norwegian version, and there is the codename of the sheets: Ark1, Ark2 etc. And I don't now if excel rename to sheet1, when open in a english version. That is why I mix the two types. 'This wont work. And I have tryed different practices with no luck. Sheet2.range("C2") = Worksheets("Ark1").range _ ("I14").Value 'This works fine Sheet2.Name = Worksheets("Ark1").range("N14").Value Aksel *** Sent via Developersdex http://www.developersdex.com *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
The differents of the sheets name and caption.
Did you try replacing worksheets("ark1") with the code name that you found in
the properties window? If you did and it didn't work, then I'm at a loss. Axel wrote: Thanks again for answer. I did not use the codename with ("Ark1"), because now I am using a norwegian version, and there is the codename of the sheets: Ark1, Ark2 etc. And I don't now if excel rename to sheet1, when open in a english version. That is why I mix the two types. 'This wont work. And I have tryed different practices with no luck. Sheet2.range("C2") = Worksheets("Ark1").range _ ("I14").Value 'This works fine Sheet2.Name = Worksheets("Ark1").range("N14").Value Aksel *** Sent via Developersdex http://www.developersdex.com *** -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
The differents of the sheets name and caption.
Hi! I found the solution. It was just to rename codename from Ark1 to Sheet1. I have to shorten the distance between my breakes. Sorry for trouble you with nothing. Aksel *** Sent via Developersdex http://www.developersdex.com *** |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
The differents of the sheets name and caption.
Or leave the codename Ark1 and use:
Sheet2.Name = Ark1.range("N14").Value Axel wrote: Hi! I found the solution. It was just to rename codename from Ark1 to Sheet1. I have to shorten the distance between my breakes. Sorry for trouble you with nothing. Aksel *** Sent via Developersdex http://www.developersdex.com *** -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using SUM for more than 30 number in differents sheets | Excel Worksheet Functions | |||
Concatenate with differents conditions | Excel Worksheet Functions | |||
Excel in differents idioms | Excel Discussion (Misc queries) | |||
How to reference win.caption, when caption changes | Excel Programming | |||
Caption | Excel Programming |