(Solved) – With Statement not executing correctly (Run-Time Error 438)

(solved)-–-with-statement-not-executing-correctly-(run-time-error-438)

I am getting a Run-time error ‘438’ saying “Object doesn’t support this property or method.”
I was previously using a code that Activated another workbook (Changes_Database Workbook), then (inside of the Changes_Database Workbook there is a sheet called Changes) the code inserted a row and shifted the others down, copied the format of the cell below, and then input a key, part and process name (a description basically, not important) alongside the date and time. This is the code (The code below is working, but it’s very slow):

Sub NewPart2()

'Sets Changes_Database as active contents and unprotects

    Set Cd = Workbooks.Open(Filename:="\FILEPATHTechnology_ChangesChanges_Database_IRR_200-2S_New.xlsm", Password:="Swarf")
    Set Changes = Cd.Sheets("Changes")

Changes.Activate
ActiveSheet.Unprotect "Swarf"

'Selects the 2nd row of the database, which is the row after the headings
ActiveSheet.Rows("2:2").Select

'Inserts a new row and shifts the other rows down
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow

'Inputs the key that is being added to the new row
ActiveSheet.Range("A2").Value = Sheet1.Range("H4").Value

'Inputs the part and process name to the new row
ActiveSheet.Range("D2").Value = UCase(Sheet1.Range("E4").Value)
ActiveSheet.Range("E2").Value = Sheet1.Range("E5").Value

'Inputs the date and time for when it was added
ActiveSheet.Range("B2").Value = Now
ActiveSheet.Range("C2").Value = Now
ActiveSheet.Range("C2").NumberFormat = "h:mm:ss AM/PM"
ActiveSheet.Range("B2").NumberFormat = "dd/mm/yyyy"


'On Error Resume Next

            ActiveSheet.Protect "Swarf"
            ActiveWorkbook.Save
            ActiveWorkbook.Close SaveChanges:=True

On Error Resume Next

End Sub

Activating the other sheet is taking quite a long time for this module to execute it’s function, so I attempted a With statement but I keep getting that error. I am trying to improve the speed of this code basically with my second Code, here it is: (SCREENSHOTS OF BOTH CODES AS WELL CAN BE FOUND BELOW)

Sub NewPart2()

Application.ScreenUpdating = False

Set y = Workbooks.Open(Filename:="\FILEPATHTechnology_ChangesChanges_Database_IRR_200-2S_New.xlsm", Password:="Swarf")

    With y

      Sheets("Changes").Unprotect "Swarf"

        .Sheets("Changes").Rows("2:2").Select
        'Inserts a new row and shifts the other rows down
        .Sheets("Changes").Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow

        'Inputs the key that is being added to the new row
        .Sheets("Changes").Range("A2").Value = Sheet1.Range("H4").Value

        'Inputs the part and process name to the new row
        .Sheets("Changes").Range("D2").Value = UCase(Sheet1.Range("E4").Value)
        .Sheets("Changes").Range("E2").Value = Sheet1.Range("E5").Value

        'Inputs the date and time for when it was added
        .Sheets("Changes").Range("B2").Value = Now
        .Sheets("Changes").Range("C2").Value = Now
        .Sheets("Changes").Range("C2").NumberFormat = "h:mm:ss AM/PM"
        .Sheets("Changes").Range("B2").NumberFormat = "dd/mm/yyyy"

      Password = "Swarf"

        .Save
        .Close False

    End With

Application.ScreenUpdating = True

End Sub

enter image description here

enter image description here

Leave a Reply

Your email address will not be published. Required fields are marked *