Buongiorno Norman,
avrei nuovamente bisogno del tuo aiuto. La famosa macro funziona perfettamente finché non proteggo i fogli. Avrei bisogno che i fogli siano protetti per evitare che accidentalmente le formule possano essere eliminate. C'è un modo per dire alla macro togli la protezione, esegui, inserisci nuovamente la protezione?
La macro per funzionare apre una seconda cartella di lavoro , può anche richiuderla?
Grazie
'========>>
Option Explicit
'-------->>
Public Sub SpostaTaskCompletati()
Dim WB1 As Workbook, WB2 As Workbook
Dim SH1 As Worksheet, SH2 As Worksheet
Dim destSH1 As Worksheet, destSH2 As Worksheet
Dim Rng1 As Range, Rng2 As Range
Dim destRng As Range, destRng2 As Range
Dim rngDelete As Range, rngDelete2 As Range
Dim rCell As Range
Dim UR As Long, UR2 As Long
Const sCartella1 As String = "TT SEGRETERIA.xlsm"
Const sCartella2 As String = "TT DIVISIONI.xlsm"
Const sFoglio1 As String = "TASK"
Const sFoglio2 As String = "TASK"
Const sFoglio_Destinazione As String = "ARCHIVIO TASK COMPLETATI"
Const sFoglio2_Destinazione As String = "ARCHIVIO TASK COMPLETATI"
Const sParola As String = "COMPLETATO"
Set WB1 = Workbooks(sCartella1)
If IsWorkbookOpen(sCartella2) Then
Set WB2 = Workbooks(sCartella2)
Else
Set WB2 = Workbooks.Open(WB1.Path & Application.PathSeparator & sCartella2)
End If
Set SH1 = WB1.Sheets(sFoglio1)
Set SH2 = WB2.Sheets(sFoglio2)
With SH1
UR = .Range("N" & .Rows.Count).End(xlUp).Row
Set Rng1 = .Range("N1:N" & UR)
End With
With SH2
UR = .Range("J" & .Rows.Count).End(xlUp).Row
Set Rng2 = .Range("J1:J" & UR)
End With
For Each rCell In Rng1.Cells
With rCell
If UCase(.Value) = sParola Then
If rngDelete Is Nothing Then
Set rngDelete = rCell
Else
Set rngDelete = Union(rngDelete, rCell)
End If
End If
End With
Next rCell
For Each rCell In Rng2.Cells
With rCell
If UCase(.Value) = sParola Then
If rngDelete2 Is Nothing Then
Set rngDelete2 = rCell
Else
Set rngDelete2 = Union(rngDelete2, rCell)
End If
End If
End With
Next rCell
If Not rngDelete Is Nothing Then
With WB1.Sheets("ARCHIVIO TASK COMPLETATI")
UR = .Range("N" & .Rows.Count).End(xlUp).Row
Set destRng = .Range("A" & UR + 1)
End With
Intersect(SH1.Columns("A:M"), rngDelete.EntireRow).Copy Destination:=destRng
rngDelete.EntireRow.Delete
End If
If Not rngDelete2 Is Nothing Then
With WB1.Sheets("ARCHIVIO TASK COMPLETATI")
UR = .Range("N" & .Rows.Count).End(xlUp).Row
Set destRng2 = .Range("N" & UR + 1)
End With
Intersect(SH2.Columns("J:T"), rngDelete2.EntireRow).Copy Destination:=destRng2
rngDelete2.EntireRow.Delete
End If
End Sub
'-------->>
Public Function IsWorkbookOpen(WBName As String) As Boolean
On Error Resume Next
IsWorkbookOpen = CBool(Len(Excel.Application.Workbooks(WBName).Name))
End Function
'<<========