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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,911
Office Version
  1. 365
Platform
  1. Windows
It's because VBA is looking at the sheet index not the sheet name.

Try this.
Code:
Set Sh = Worksheets(CStr(Range("D6").Value))
 

bsnapool

Active Member
Joined
Jul 10, 2006
Messages
452
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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,911
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,111,910
Messages
5,541,533
Members
410,547
Latest member
htran4
Top