Problem mit Activate Methode
06.06.2018 09:19:27
Lisa
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