13.09.2020, 10:53
Hallo Sabina,
vielen Dank für deine schnelle Hilfe.
Ich bin jetzt dazu gekommen das zu ändern.
Ich habe die Zeilen so eingefügt wie du geschrieben hast:
With Range(Cells(mySelection.Row + 91, mySelection.Column - 15), Cells(mySelection.Row + 91, mySelection.Column + 260))
.Value = .Value
End With
Leider hat das Makro keinen Wert umgewandelt, es bleiben die Formeln in den Feldern.
Da ich mir das nicht erklären kann, hier das komplette Teil:
msg = MsgBox("Ist der richtige Tag (Heute) auf M5 makiert? Sind die Angaben der LW/PA-Aufstellung in Ordnung?", vbYesNo)
If msg = vbNo Then
Exit Sub
Else
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"S:\..." _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Application.DisplayAlerts = True
Application.ScreenUpdating = False
Sheets("M51").Select
ActiveSheet.Range("$A$1:$JB$3861").AutoFilter Field:=1
Sheets("M6").Select
ActiveSheet.Range("$A$1:$IZ$3405").AutoFilter Field:=1
ActiveWindow.SmallScroll Down:=3
Sheets("M7").Select
ActiveSheet.Range("$A$1:$JB$3405").AutoFilter Field:=1
Sheets("M8").Select
ActiveSheet.Range("$A$1:$IZ$4413").AutoFilter Field:=1
Sheets("KB").Select
ActiveSheet.Range("$A$1:$IZ$4413").AutoFilter Field:=1
Sheets("M51").Select
Dim zelle As Range
Dim mySelection As Range
Set mySelection = Selection
With Range(Cells(mySelection.Row + 91, mySelection.Column - 15), Cells(mySelection.Row + 91, mySelection.Column + 260))
.Value = .Value
End With
ActiveSheet.Range("$A$1:$JA$2915").AutoFilter Field:=1, Criteria1:="="
Sheets("M6").Select
With Range(Cells(mySelection.Row + 91, mySelection.Column - 15), Cells(mySelection.Row + 91, mySelection.Column + 260))
.Value = .Value
End With
ActiveSheet.Range("$A$1:$IZ$2853").AutoFilter Field:=1, Criteria1:="="
Sheets("M7").Select
With Range(Cells(mySelection.Row + 91, mySelection.Column - 15), Cells(mySelection.Row + 91, mySelection.Column + 260))
.Value = .Value
End With
ActiveSheet.Range("$A$1:$JA$2853").AutoFilter Field:=1, Criteria1:="="
Sheets("M8").Select
With Range(Cells(mySelection.Row + 91, mySelection.Column - 15), Cells(mySelection.Row + 91, mySelection.Column + 260))
.Value = .Value
End With
ActiveSheet.Range("$A$1:$IZ$2853").AutoFilter Field:=1, Criteria1:="="
Sheets("KB").Select
With Range(Cells(mySelection.Row + 91, mySelection.Column - 15), Cells(mySelection.Row + 91, mySelection.Column + 260))
.Value = .Value
End With
ActiveSheet.Range("$A$1:$JA$2853").AutoFilter Field:=1, Criteria1:="="
Sheets("Daten").Select
Application.ScreenUpdating = True
ActiveWorkbook.Save
End If
msg = MsgBox("Tag in Werte umgewandelt", vbOKOnly)
End Sub
vielen Dank für deine schnelle Hilfe.
Ich bin jetzt dazu gekommen das zu ändern.
Ich habe die Zeilen so eingefügt wie du geschrieben hast:
With Range(Cells(mySelection.Row + 91, mySelection.Column - 15), Cells(mySelection.Row + 91, mySelection.Column + 260))
.Value = .Value
End With
Leider hat das Makro keinen Wert umgewandelt, es bleiben die Formeln in den Feldern.
Da ich mir das nicht erklären kann, hier das komplette Teil:
msg = MsgBox("Ist der richtige Tag (Heute) auf M5 makiert? Sind die Angaben der LW/PA-Aufstellung in Ordnung?", vbYesNo)
If msg = vbNo Then
Exit Sub
Else
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"S:\..." _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Application.DisplayAlerts = True
Application.ScreenUpdating = False
Sheets("M51").Select
ActiveSheet.Range("$A$1:$JB$3861").AutoFilter Field:=1
Sheets("M6").Select
ActiveSheet.Range("$A$1:$IZ$3405").AutoFilter Field:=1
ActiveWindow.SmallScroll Down:=3
Sheets("M7").Select
ActiveSheet.Range("$A$1:$JB$3405").AutoFilter Field:=1
Sheets("M8").Select
ActiveSheet.Range("$A$1:$IZ$4413").AutoFilter Field:=1
Sheets("KB").Select
ActiveSheet.Range("$A$1:$IZ$4413").AutoFilter Field:=1
Sheets("M51").Select
Dim zelle As Range
Dim mySelection As Range
Set mySelection = Selection
With Range(Cells(mySelection.Row + 91, mySelection.Column - 15), Cells(mySelection.Row + 91, mySelection.Column + 260))
.Value = .Value
End With
ActiveSheet.Range("$A$1:$JA$2915").AutoFilter Field:=1, Criteria1:="="
Sheets("M6").Select
With Range(Cells(mySelection.Row + 91, mySelection.Column - 15), Cells(mySelection.Row + 91, mySelection.Column + 260))
.Value = .Value
End With
ActiveSheet.Range("$A$1:$IZ$2853").AutoFilter Field:=1, Criteria1:="="
Sheets("M7").Select
With Range(Cells(mySelection.Row + 91, mySelection.Column - 15), Cells(mySelection.Row + 91, mySelection.Column + 260))
.Value = .Value
End With
ActiveSheet.Range("$A$1:$JA$2853").AutoFilter Field:=1, Criteria1:="="
Sheets("M8").Select
With Range(Cells(mySelection.Row + 91, mySelection.Column - 15), Cells(mySelection.Row + 91, mySelection.Column + 260))
.Value = .Value
End With
ActiveSheet.Range("$A$1:$IZ$2853").AutoFilter Field:=1, Criteria1:="="
Sheets("KB").Select
With Range(Cells(mySelection.Row + 91, mySelection.Column - 15), Cells(mySelection.Row + 91, mySelection.Column + 260))
.Value = .Value
End With
ActiveSheet.Range("$A$1:$JA$2853").AutoFilter Field:=1, Criteria1:="="
Sheets("Daten").Select
Application.ScreenUpdating = True
ActiveWorkbook.Save
End If
msg = MsgBox("Tag in Werte umgewandelt", vbOKOnly)
End Sub