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?
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