Bene sono riuscito attraverso il registratore di macro a creare questa macro
che comunque funziona però ci sono un bel pò di spostamenti in più durante la copia ,mi potete aiutare a pulirla un tantino?
fermo restando tutti i dati perchè ho già iniziato a lavorarci
- Codice: Seleziona tutto
Sub CopiaAnno()
'
' CopiaAnno Macro
MsgBox "Attenzione chiusura annuale Selezione nella cella AH36 ( Si O NO )"
If Sheets("DIC").Range("AH36") = "S" Then '<<<<<<<<<Condizione di conferma se salvare o non salvare
Call CopiaAnno
Else:
MsgBox ("Dati non Caricati ")
End If
Exit Sub
[quote]MsgBox "Attenzione chiusura annuale Selezione nella cella AH36 ( Si O NO )"
If Sheets("DIC").Range("AH36") = "S" Then '<<<<<<<<<Condizione di conferma se salvare o non salvare
Call CopiaAnno
Else:
MsgBox ("Dati non Caricati ")
End If
Exit Sub[/quote]
'
'
'
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<CODIA I DATI in DBvendita
'GENNAIO
Sheets("VERIFICA").Select ' <<<<<<<<<<<<<<<<<<<<<<scopre il DBvendita
Sheets("DBvendita").Visible = True
Sheets("DBvendita").Select
ActiveSheet.Unprotect
Sheets("GEN").Select
Range("C3:P40").Select
Selection.Copy
Sheets("DBvendita").Select
Range("B1").Select
Selection.End(xlDown).Select
Range("C1048576").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=18
ActiveWindow.SmallScroll ToRight:=4
ActiveCell.Offset(37, 14).Range("A1").Select
Selection.End(xlDown).Select
Range("R1048576").Select
Selection.End(xlUp).Select
'
'FEBBRAIO
Application.CutCopyMode = False
ActiveSheet.Unprotect
Sheets("FEB").Select
Range("c3:P40").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("DBvendita").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(37, 14).Range("A1").Select
Selection.End(xlDown).Select
Range("AG1048576").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
'MARZO
Application.CutCopyMode = False
ActiveSheet.Unprotect
Sheets("MAR").Select
Range("c3:P40").Select
'ActiveSheet.Unprotect
Application.CutCopyMode = False
Selection.Copy
Sheets("DBvendita").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(37, 14).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
'APRILE
Application.CutCopyMode = False
ActiveSheet.Unprotect
Sheets("APR").Select
Range("c3:P40").Select
'ActiveSheet.Unprotect
Application.CutCopyMode = False
Selection.Copy
Sheets("DBvendita").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll ToRight:=14
ActiveWindow.SmallScroll Down:=18
ActiveCell.Offset(37, 14).Range("A1").Select
Selection.End(xlDown).Select
Range("BK1048576").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
'MAGGIO
Application.CutCopyMode = False
ActiveSheet.Unprotect
Sheets("MAG").Select
Range("c3:P40").Select
'ActiveSheet.Unprotect
Application.CutCopyMode = False
Selection.Copy
Sheets("DBvendita").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(37, 15).Range("A1").Select
Selection.End(xlDown).Select
Range("CA1048576").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
'GIUGNO
Application.CutCopyMode = False
ActiveSheet.Unprotect
Sheets("GIU").Select
Range("c3:P40").Select
'ActiveSheet.Unprotect
Application.CutCopyMode = False
Selection.Copy
Sheets("DBvendita").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(37, 14).Range("A1").Select
Selection.End(xlDown).Select
Range("CP1048576").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
'LUGLIO
Application.CutCopyMode = False
ActiveSheet.Unprotect
Sheets("LUG").Select
Range("c3:P40").Select
'ActiveSheet.Unprotect
Application.CutCopyMode = False
Selection.Copy
Sheets("DBvendita").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(37, 14).Range("A1").Select
Selection.End(xlDown).Select
Range("DE1048576").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
'AGOSTO
Application.CutCopyMode = False
ActiveSheet.Unprotect
Sheets("AGO").Select
Range("c3:P40").Select
' ActiveSheet.Unprotect
Application.CutCopyMode = False
Selection.Copy
Sheets("DBvendita").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(37, 14).Range("A1").Select
Selection.End(xlDown).Select
Range("DT1048576").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
'SETTEMBRE
Application.CutCopyMode = False
ActiveSheet.Unprotect
Sheets("SET").Select
Range("c3:P40").Select
' ActiveSheet.Unprotect
Application.CutCopyMode = False
Selection.Copy
Sheets("DBvendita").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll ToRight:=25
ActiveWindow.SmallScroll Down:=12
ActiveCell.Offset(38, 14).Range("A1").Select
Selection.End(xlDown).Select
Range("EI1048576").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
'OTTOBRE
Application.CutCopyMode = False
ActiveSheet.Unprotect
Sheets("OTT").Select
Range("c3:P40").Select
' ActiveSheet.Unprotect
Application.CutCopyMode = False
Selection.Copy
Sheets("DBvendita").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=12
ActiveCell.Offset(37, 14).Range("A1").Select
ActiveWindow.SmallScroll ToRight:=6
Selection.End(xlDown).Select
Range("EX1048576").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
'NOVEMBRE
Application.CutCopyMode = False
ActiveSheet.Unprotect
Sheets("NOV").Select
Range("c3:P40").Select
'ActiveSheet.Unprotect
Application.CutCopyMode = False
Selection.Copy
Sheets("DBvendita").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=16
ActiveWindow.SmallScroll ToRight:=4
ActiveCell.Offset(37, 14).Range("A1").Select
Selection.End(xlDown).Select
Range("FM1048576").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
'DICEMBRE
Application.CutCopyMode = False
ActiveSheet.Unprotect
Sheets("DIC").Select
Range("c3:P40").Select
'ActiveSheet.Unprotect
Application.CutCopyMode = False
Selection.Copy
Sheets("DBvendita").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=16
ActiveWindow.SmallScroll ToRight:=1
ActiveCell.Offset(37, 14).Range("A1").Select
Selection.End(xlDown).Select
Range("GB1048576").Select
Selection.End(xlUp).Select
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
'<<<<<COPIA I DATI in DBacquisti
'
' GENNAIO
Sheets("VERIFICA").Select
Sheets("DBacquisti").Visible = True '<<<<<<<<<<<<<<scopre il DBacquisti
Sheets("DBacquisti").Select
ActiveSheet.Unprotect
Sheets("GEN").Select
Range("AH50:AJ53").Select
Selection.Copy
Sheets("DBacquisti").Select
Range("C1").Select
Selection.End(xlDown).Select
Range("D1048576").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(3, 3).Range("A1").Select
Selection.End(xlDown).Select
Range("I1048576").Select
Selection.End(xlUp).Select
'FEBBRAIO
Sheets("FEB").Select
Range("AH50:AJ53").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("DBacquisti").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(3, 3).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.End(xlUp).Select
'MARZO
Sheets("MAR").Select
Range("AH50:AJ53").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("DBacquisti").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(3, 3).Range("A1").Select
Selection.End(xlDown).Select
Range("Q1048576").Select
Selection.End(xlUp).Select
'APRILE
Sheets("APR").Select
ActiveWindow.SmallScroll Down:=36
Range("AH50:AJ53").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("DBacquisti").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(3, 3).Range("A1").Select
Selection.End(xlDown).Select
Range("U1048576").Select
Selection.End(xlUp).Select
'MAGGIO
Sheets("MAG").Select
ActiveWindow.SmallScroll Down:=36
Range("AH50:AJ53").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("DBacquisti").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(3, 3).Range("A1").Select
Selection.End(xlDown).Select
Range("Y1048576").Select
Selection.End(xlUp).Select
'GIUGNO
Sheets("GIU").Select
ActiveWindow.SmallScroll Down:=36
Range("AH50:AJ53").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("DBacquisti").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(3, 3).Range("A1").Select
Selection.End(xlDown).Select
Range("AC1048576").Select
Selection.End(xlUp).Select
'LUGLIO
Sheets("LUG").Select
ActiveWindow.SmallScroll Down:=36
Range("AH50:AJ53").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("DBacquisti").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(3, 3).Range("A1").Select
Selection.End(xlDown).Select
Range("AG1048576").Select
Selection.End(xlUp).Select
'AGOSTO
Sheets("AGO").Select
ActiveWindow.SmallScroll Down:=36
Range("AH50:AJ53").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("DBacquisti").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(3, 3).Range("A1").Select
Selection.End(xlDown).Select
Range("AK1048576").Select
Selection.End(xlUp).Select
'SETTEMBRE
Sheets("SET").Select
ActiveWindow.SmallScroll Down:=36
Range("AH50:AJ53").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("DBacquisti").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(3, 3).Range("A1").Select
Selection.End(xlDown).Select
Range("AO1048576").Select
Selection.End(xlUp).Select
'OTTOBRE
Sheets("OTT").Select
ActiveWindow.SmallScroll Down:=36
Range("AH50:AJ53").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("DBacquisti").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(3, 3).Range("A1").Select
Selection.End(xlDown).Select
Range("AT1048576").Select
Selection.End(xlUp).Select
'NOVEMBRE
Sheets("NOV").Select
ActiveWindow.SmallScroll Down:=36
Range("AH50:AJ53").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("DBacquisti").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(3, 3).Range("A1").Select
Selection.End(xlDown).Select
Range("AY1048576").Select
Selection.End(xlUp).Select
'DICEMBRE
Sheets("DIC").Select
ActiveWindow.SmallScroll Down:=36
Range("AH50:AJ53").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("DBacquisti").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(3, 3).Range("A1").Select
Selection.End(xlDown).Select
Range("BD1048576").Select
Selection.End(xlUp).Select
'
'
'<<<CANCELLA I DATI dell'ANNO appena passato
'
'
Sheets("DIC").Select
ActiveWindow.SmallScroll Down:=3
Range("G8:L38,N8:P38").Select
Range("AH36").Select
Application.CutCopyMode = False
Selection.ClearContents
Sheets("NOV").Select
Range("G8:L37,N8:P37").Select
Range("N37").Activate
Selection.ClearContents
Sheets("OTT").Select
Range("G8:L38,N8:P38").Select
Range("N38").Activate
Selection.ClearContents
Sheets("SET").Select
Range("G8:L37,N8:P37").Select
Range("N37").Activate
Selection.ClearContents
Sheets("AGO").Select
Range("G8:L38,N8:P38").Select
Range("N38").Activate
Selection.ClearContents
Sheets("LUG").Select
Range("G8:L38,N8:P38").Select
Range("N38").Activate
Selection.ClearContents
Sheets("GIU").Select
Range("G8:L38,N8:P38").Select
Range("N38").Activate
Selection.ClearContents
Sheets("MAG").Select
Range("G8:L38,N8:P38").Select
Range("N38").Activate
Selection.ClearContents
Sheets("APR").Select
Range("G8:L37,N8:P37").Select
Range("N37").Activate
Selection.ClearContents
Sheets("MAR").Select
Range("G8:L38,N8:P38").Select
Range("N38").Activate
Selection.ClearContents
Sheets("FEB").Select
Range("G8:L38,N8:P38").Select
Range("N38").Activate
'Selection.ClearContents
Sheets("GEN").Select
ActiveWindow.SmallScroll Down:=-39
Range("G8:L38,N8:P38").Select
Range("N38").Activate
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-6
'
'<<<<<<<<<<<<<RIPROTEGGE I FOGLI DI LAVORO
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("G8").Select
Sheets("FEB").Select
ActiveWindow.SmallScroll Down:=-36
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("G8").Select
Sheets("MAR").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("G8").Select
Sheets("APR").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("G8").Select
Sheets("MAG").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("G8").Select
Sheets("GIU").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("G8").Select
Sheets("LUG").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("G8").Select
Sheets("AGO").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("G8").Select
Sheets("SET").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("G8").Select
Sheets("OTT").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("G8").Select
Sheets("NOV").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("G8").Select
Sheets("DIC").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("L38").Select
'
Application.CutCopyMode = False
ActiveSheet.Unprotect
'<<<<<<<<<<nasconde il DBvendita
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("DBvendita").Select
ActiveWindow.SelectedSheets.Visible = Fal
'<<<<<<<<<<nasconde il DBacquisti
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("DBacquisti").Select
ActiveWindow.SelectedSheets.Visible = Fal
Sheets("GEN").Select '<<<<<<Si posiziona sulla cella G8 di gennaio
Range("G8").Select
ThisWorkbook.Save '<<<<<<<<Salva il file
Exit Sub
End If
End Sub
in riferimento alla condizione di un eventuale errore di lancio della macro non mi funziona Perche?
..
CopiaAnno Macro
MsgBox "Attenzione chiusura annuale Selezione nella cella AH36 ( Si O NO )"
If Sheets("DIC").Range("AH36") = "S" Then '<<<<<<<<<Condizione di conferma se salvare o non salvare
Call CopiaAnno
Else:
MsgBox ("Dati non Caricati ")
End If
Exit Sub
grazie per l'aiuto