Hey guys, I'm having a problem with the below code where I get the error in the title on the code that I've highlighted. What's strange is that it worked fine last night, but isn't working now, and I have no idea why
Any help is greatly appreciated!
When the worksheet is activated, it calls this code below.
Any help is greatly appreciated!
Code:
Sub Submit_Data_FrontOffice()
Dim X As Integer
X = 8
Dim LastRowNumber As Integer
Dim RowNum
LastRowNumber = 0
RowNum = X
ActiveSheet.Select
Range("C" & X).Select
UnProtect_Sheet
Do While ActiveCell.Value <> ""
LastRowNumber = ActiveCell.Row
RowNum = RowNum + 1
Range("C" & RowNum).Select
Loop
MsgBox RowNum
Range("C5:Q" & RowNum - 1).Select
Selection.Copy
Dim ColNum As Integer
ActiveWorkbook.Unprotect ("Pa55w0rd#153")
Worksheets("Front Office - Master Data").Activate
ColNum = ActiveSheet.Cells(4, Columns.Count).End(xlToLeft).Column
MsgBox ColNum
UnProtect_Sheet
ActiveSheet.Cells(1, ColNum + 1).Select
With Selection
.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ 'This is where the error occurs.
xlNone, SkipBlanks:=False, Transpose:=False
End With
Range("A1").Select
Protect_Sheet
Application.CutCopyMode = False
Worksheets("Front Office - Actual").Select
Range("D8:Q" & RowNum - 1).Select
Selection.ClearContents
Range("D8").Select
ActiveWorkbook.Protect ("Pa55w0rd#153"), Structure:=True, Windows:=False
End Sub
When the worksheet is activated, it calls this code below.
Code:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
' List all protected sheets here '
If Sh.Name = "Staff List - Salaried" Or Sh.Name = "Staff List - Fortnightly" Or Sh.Name = "Front Office - Forecast" Or Sh.Name = "Front Office - Actual" Or Sh.Name = "Front Office - Master Data" Or Sh.Name = "Food & Beverage" Or Sh.Name = "Admin" Or Sh.Name = "*******" Or Sh.Name = "Housekeeping" Or Sh.Name = "Lookup" Then
bFinished = False
For Each sName In Split(ActiveWorkbook.CustomDocumentProperties("auth").Value, ":")
If Sh.Name = sName Then bFinished = True
Next sName
If Not bFinished Then
Sh.Visible = False
MsgBox "You don't have authorization to view that sheet!"
End If
End If
End Sub
Sub UnPro