[VBA] File saving does not work/new version not uploaded

[Excelquestion35]

Hi all,

Hope someone is able to help me with a macro that has given me some headaches already.
What my macro does is basically the following:
We have a list of line managers, in case a new one comes in we want to make sure that that person is taken into account in the masterfile.
Since we are working with another document containing the line managers as well, the name should be stored in another document as well.
The masterfile is the file where the code is running from. The SmartTime form is the file that has to be opened and adjusted as the macro runs. And here is what is causing me some trouble.

I have tried some different approaches already but I either get the following:
1) Macro runs perfectly, asks me whether I want to override the file and save the SmartTime form. I press yes and the changes won't appear/the new version is not uploaded.
Yet, I see the changes being made in a step by step run mode.
2) Object variable can not be found the next time I run the macro for

Set wsSupervisor = wbForm.Sheets("Supervisor (leidinggevende)")

. From what I can see; the file is opened but somehow it is a blank file with one sheet being named after the document file name. Of course this is not how I set-up the document.

The piece of code where it runs into error:

 ' Open the RPA SmartTime Form workbook and set worksheet
    On Error Resume Next
    Set wbForm = Workbooks.Open("https://XXX.sharepoint.com/:x:/r/teams/XXX/Shared%20Documents/General/SmartTime-form/RPA-SmartTime-Form-UZK-WH-Macro.xlsx?d=w17e230e024b9466d9e24a3d49d959d3e&csf=1&web=1&e=Eg8ZmI")
    Application.Wait (Now + TimeValue("0:00:03"))
    If Err.Number <> 0 Then
        MsgBox "Could not open workbook. Please check the file URL."
        Exit Sub
    End If
    On Error GoTo 0
    Set wsSupervisor = wbForm.Sheets("Supervisor (leidinggevende)")

I have a feeling that has to do with the way I open the Sharepoint file. I am now unsure what the right way of referring to the file is, I have some options (for privacy reasons, some parts replaced with XXX):
1) https://xxx.sharepoint.com//r/teams/XXX/Shared Documents/General/SmartTime-form/RPA-SmartTime-Form-UZK-WH-Macro.xlsx?d=w17e230e024b9466d9e24a3d49d959d3e&csf=1&web=1&e=wf3MTU
2) https://xxx.sharepoint.com//r/teams/XXX/Shared Documents/General/SmartTime-form/RPA-SmartTime-Form-UZK-WH-Macro.xlsx
3) https://xxx.sharepoint.com//r/teams/XXX/_layouts/15/Doc.aspx?sourcedoc={17E230E0-24B9-466D-9E24-A3D49D959D3E}&file=RPA-SmartTime-Form-UZK-WH-Macro.xlsx&action=default&mobileredirect=true

Also with trying all three one by one I could not fix it.
Any clue on what I might be doing wrong?

Your help is much appreciated!

Whole macro:

Sub UpdateSheets()

    Dim wbForm As Workbook
    Dim wsChange As Worksheet
    Dim wsFLMs As Worksheet
    Dim wsSupervisor As Worksheet
    Dim lRow As Long
    Dim rng As Range
    Dim rngCopy As Range
    Dim strSite As String
    Dim strFLM As String
   

    ' Set worksheets
    Set wsChange = ThisWorkbook.Sheets("FLM-change")
    Set wsFLMs = ThisWorkbook.Sheets("FLMs")

    ' Get the Site and FLM values
    strSite = wsChange.Range("G17").Value
    strFLM = wsChange.Range("G18").Value

    ' Apply filters and find the old data
    wsFLMs.Rows(3).AutoFilter Field:=2, Criteria1:=strSite
    wsFLMs.Rows(3).AutoFilter Field:=3, Criteria1:=strFLM

    ' Find the last row with data in columns B or C
    lRow = wsFLMs.Cells(wsFLMs.Rows.Count, "B").End(xlUp).Row
   
    ' Insert the new row
    wsFLMs.Rows(lRow + 1).EntireRow.Insert
   
    ' Copy the values from the original row to the new row
    wsFLMs.Rows(lRow).Copy Destination:=wsFLMs.Rows(lRow + 1)
   
    ' Update the values in the new row
    wsFLMs.Cells(lRow + 1, "C").Value = wsChange.Range("G13").Value
    wsFLMs.Cells(lRow + 1, "I").Value = wsChange.Range("G12").Value
    wsFLMs.Cells(lRow + 1, "D").Value = "Y"
    wsFLMs.Cells(lRow + 1, "E").Value = wsChange.Range("G18").Value
   
    ' Clear the filter
    wsFLMs.AutoFilterMode = False
   
    ' Apply a new search
    wsFLMs.Rows(3).AutoFilter Field:=2, Criteria1:=strSite
   
    ' Reset lRow to the last row of the visible data
    lRow = wsFLMs.Cells(wsFLMs.Rows.Count, "B").End(xlUp).Row
   
    ' Copy the range of the visible cells
    Set rngCopy = wsFLMs.Range("C4:C" & lRow).SpecialCells(xlCellTypeVisible)
    rngCopy.Copy

   
    ' Open the RPA SmartTime Form workbook and set worksheet
    On Error Resume Next
    Set wbForm = Workbooks.Open("https://XXX.sharepoint.com/:x:/r/teams/XXX/Shared%20Documents/General/SmartTime-form/RPA-SmartTime-Form-UZK-WH-Macro.xlsx?d=w17e230e024b9466d9e24a3d49d959d3e&csf=1&web=1&e=Eg8ZmI")
    Application.Wait (Now + TimeValue("0:00:03"))
    If Err.Number <> 0 Then
        MsgBox "Could not open workbook. Please check the file URL."
        Exit Sub
    End If
    On Error GoTo 0
    Set wsSupervisor = wbForm.Sheets("Supervisor (leidinggevende)")

    ' Unprotect and unhide the worksheet
    wsSupervisor.Unprotect Password:="XXXX" 
    wsSupervisor.Visible = xlSheetVisible

    ' Find the column with the Site value and paste the data
    'Dim col As Long
    'col = wsSupervisor.Rows(1).Find(What:=strSite, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False).Column
    'wsSupervisor.Cells(2, col).Resize(rngCopy.Rows.Count, rngCopy.Columns.Count).Value = rngCopy.Value
   
    ' Find the column with the Site value and clear previous data
    Dim col As Long
    Dim foundCell As Range
    Set foundCell = wsSupervisor.Rows(1).Find(What:=strSite, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
   
    If Not foundCell Is Nothing Then
        col = foundCell.Column
   
        ' Clear the contents of the cells from row 2 to the last row with data
        lastrow = wsSupervisor.Cells(wsSupervisor.Rows.Count, col).End(xlUp).Row
        wsSupervisor.Range(wsSupervisor.Cells(2, col), wsSupervisor.Cells(lastrow, col)).ClearContents
   
        ' Paste the new data
        wsSupervisor.Cells(2, col).Resize(rngCopy.Rows.Count, rngCopy.Columns.Count).Value = rngCopy.Value
   
    Else
        ' handle situation when strSite is not found
        MsgBox "The name of the Management Unit does not exist in this file. Please add the Management Unit manually in first cell of a random column.", vbInformation
    End If


    ' Hide and protect the worksheet
    wsSupervisor.Visible = xlSheetHidden
    'wsSupervisor.Protect Password:="XXXX"

    ' Save and close the workbooks
    wbForm.Save
    wbForm.Close

    ' Clear any filters and sort Column B in the FLMs sheet
    If wsFLMs.AutoFilterMode Then
        wsFLMs.AutoFilterMode = False
    End If
   
    With wsFLMs.Rows("5:" & wsFLMs.Rows.Count)
        .Sort Key1:=wsFLMs.Range("B5"), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
        MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    End With
   
    ' Apply the filter back to Row 3
    wsFLMs.Range("A3:W3").AutoFilter
   
    ThisWorkbook.Save
    MsgBox "Macro ran successfully", vbInformation

End Sub

 

[Excelquestion35]

Bump

 

[NdNoviceHlp]

Hi Excelquestion35. Not sure about what's going on with the sharepoint saving (or lack thereof), but this looks wrong...

Set rngCopy = wsFLMs.Range("C4:C" & lRow).SpecialCells(xlCellTypeVisible)
'rngCopy.Copy
' Paste the new data
wsSupervisor.Cells(2, col).Resize(rngCopy.Rows.Count, rngCopy.Columns.Count).Value = rngCopy.Cells.Value

No need to copy the range before you resize it. You could go to the sharepoint file that you want to open and run this code to make sure your file path is correct...

 Sub test()
MsgBox ThisWorkbook.FullName
End Sub

Beyond this, I don't have any further suggestions. Good luck. Dave

 

[Excelquestion35]

Thank you, the file path code made me realise that there was a mismatch somewhere! I used Sharepoint for getting the link while I used Teams to check te file.

 

[NdNoviceHlp]

You are welcome. Thanks for posting your outcome. Dave