Sheet Name integers??

bsnapool

Active Member
Joined
Jul 10, 2006
Messages
452
I have this code and somethin is very strange, Say I enter 1 in D6, and press the button, it copies the data to tab 12....

Enter 2 and it says the sheet does not exsist but it does... STRANGE!!

Any ideas why anyone?


Code:
Sub MyButtonPress() 
    Dim Sh As Worksheet, Msg As VbMsgBoxResult 
         Msg = MsgBox("PLEASE CHECK ALL SICKNESS DATA IS CORRECT!!  Do you wish to copy the data to week " & [D6] & "?", vbYesNo) 
        If Msg = vbNo Then Exit Sub 
        On Error Resume Next 
        Set Sh = Worksheets(Range("D6").Value) 
        On Error GoTo 0 
        If Not Sh Is Nothing Then 
            With Sh 
                 Cells.Copy .Cells 
                .Tab.ColorIndex = 3 
                
            End With 
        Else 
            MsgBox "Sheet " & [D6] & " does not exist" 
        End If 
End Sub
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
It's because VBA is looking at the sheet index not the sheet name.

Try this.
Code:
Set Sh = Worksheets(CStr(Range("D6").Value))
 
Upvote 0
Norie that was lightning quick!!

Excellent, it worked!!

Could you give me more details about this please?

It's because VBA is looking at the sheet index not the sheet name
 
Upvote 0
I don't really know the technical explanation, but when you use a integer to identify sheets in code then it's assumed you are referring to the sheet by it's sheet index, not the actual name.

By using CStr the number is converted to a string and VBA recognises that you are referring to the sheet's name not it's index.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top