The differents of the sheets name and caption.

[Axel]

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

 

[Dave Peterson]

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]

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.

 

[Dave Peterson]

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]

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

 

[Dave Peterson]

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]

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

 

[Dave Peterson]

Or leave the codename Ark1 and use:

Sheet2.Name = Ark1.range("N14").Value