Runtime Erro 1004 - Application-defined error or Object-defined Error

MikeyL75

New Member
Joined
Jul 29, 2014
Messages
10
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 :confused:
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
It's in the first code section, I marked it with " 'This is where the error occurs.", apologies that it doesn't stand out.
 
Upvote 0
I think you will find that unprotecting the worksheet clears the clipboard. So you will need to move the Copying so that it is after the Unprotection.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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