danibi60 ha scritto:Le colonne B-C-E-H saranno sempre riempite di volta in volta con testi che variano (te ne allego un esempio) e lo saranno sempre.
Non niente sfondo giallo, solo valori.
Questo il codice da copiare in un modulo standard (Modulo1):
- Codice: Seleziona tutto
'---------------------------------------------------------------------------------------
' Procedure : Ricopia
' Author : scossa
' Date : 20/07/2013
' Purpose :
'---------------------------------------------------------------------------------------
'
Public Sub Ricopia()
Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Dim rngFr As Range
Dim rngTo As Range
Dim cella As Range
Dim bCalc As XlCalculation
With Application
bCalc = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
Set wb = ThisWorkbook
Set ws = wb.ActiveSheet
Set rng = ws.Range("A1").CurrentRegion '.Resize(, 8)
Set rngTo = ws.Range("A31")
rngTo.CurrentRegion.ClearContents
On Error Resume Next
Set rngFr = rng.Columns(1).Offset(0, 8).SpecialCells(xlCellTypeConstants, xlTextValues)
If Err.Number = 0 Then
Set rng = Intersect(rngFr.EntireRow, rng)
rng.Copy rngTo
End If
Set rng = ws.Range("L1").CurrentRegion '.Resize(, 8)
Err.Clear
Set rngFr = rng.Columns(1).Offset(0, 8).SpecialCells(xlCellTypeConstants, xlTextValues)
If Err.Number = 0 Then
Set rng = Intersect(rngFr.EntireRow, rng)
rng.Copy rngTo.Offset(rngTo.CurrentRegion.Rows.Count)
End If
'set rngto = rngto.CurrentRegion.Resize(.
On Error GoTo 0
rngTo.CurrentRegion.ClearFormats 'per eliminare il formato
With Application
.Calculation = bCalc
.ScreenUpdating = True
End With
Set rng = Nothing
Set rngFr = Nothing
Set rngTo = Nothing
Set ws = Nothing
Set wb = Nothing
End Sub
Cancella tutto il codice presente nel modulo di Foglio1 tranne:
- Codice: Seleziona tutto
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("I1:I25,T1:T25")) Is Nothing Then
Application.EnableEvents = False
Call Ricopia
Application.EnableEvents = True
End If
End Sub
Cancella tutti i codici degli altri moduli (Modulo2, Modulo3 etc.)