Hallo zusammen, 

ich habe ein makro geschrieben, das Daten aus einem Worksheet ausliest, eine Berechnung durchführt und auf ein anderes Worksheet überträgt. 
Lokal funktioniert das ganze einwandfrei. Nachdem ich es auf den Sharepoint hochgeladen habe, bekomme ich den Laufzeitfehler 1004 - Die Activate - Methode für das Objekt Worksheet ist fehlgeschlagen. Ich weiß, dass man nicht unbedingt mit Activate arbeiten muss aber ich fand es leichter bzw. war nicht in der Lage die entsprechenden Zellen ohne die Activate Methode anzusprechen. 

Vielen Dank schonmal!


 

Option Explicit

Sub UpdateReportingPercentage()
    Dim curYear As Integer
    Dim curMonth As Integer
    Dim curCell As Range
    Dim curRow As Integer
    Dim curCol As Integer
    Dim Employees As Worksheet
    Dim Reporting As Worksheet
    
    Set Employees = ThisWorkbook.Sheets("Employees_trained")
    Set Reporting = ThisWorkbook.Sheets("Reporting")

    Application.ScreenUpdating = False
        

    curYear = year(Now())
    curMonth = month(Now())
    curRow = 10
    curCol = 2
    
    Employees.Activate                              'Search for current month in table Employees trained
    
    Set curCell = Cells(curRow, curCol)
    While curCell.Value <> curYear
        curCol = curCol + 1
        Set curCell = Cells(curRow, curCol)
    Wend
    curRow = curRow + 1
    Set curCell = Cells(curRow, curCol)
    While curCell.Value <> curMonth
        curCol = curCol + 1
        Set curCell = Cells(curRow, curCol)
    Wend

    'curCell.Select
    
    
    With Reporting                                          'copy Data to Reporting
        .Unprotect
        AuthorizedTrained curRow, curCol, Reporting         'Calculation of Percentage for authorized employees
        AffectedTrained curRow, curCol, Reporting           'Calculation of Percentage for affected employees
        .Protect
        '.Activate
    End With
    
    Reporting.Activate

    Application.ScreenUpdating = True

End Sub

Sub AuthorizedTrained(ByVal curRow As Integer, ByVal curCol As Integer, ByRef Reporting As Worksheet) 'Calculation of Percentage for authorized employees

    Dim authorized As Double
    Dim curCell As Range

    Set curCell = Cells(curRow + 5, curCol)
    With Reporting
        authorized = curCell.Value / Cells(16, 32).Value
        .Cells(14, 42) = authorized
    End With


End Sub

Sub AffectedTrained(ByVal curRow As Integer, curCol As Integer, ByRef Reporting As Worksheet) 'Calculation of Percentage for affected employees

    Dim affected As Double
    Dim curCell As Range

    Set curCell = Cells(curRow + 6, curCol)
    
    With Reporting
        affected = curCell.Value / Cells(17, 32).Value
        .Cells(15, 42) = affected
    End With


End Sub