pensavo di aver risolto ma provando il file è uscito fuori un'altro problema
avevo fatto questa macro su foglio 1^ e logicamente funziona benissimo
facendo il copia e incolla sulle altre 35 giornate logicamente nn funziona alla meraviglia dato che non mi ordina le formazioni per T e poi 1 2 3 4 5 6 7..
la mia domanda è questa esiste qualche siglia o codice che mi permetta di far funzionare la macro anche sugli altri fogli oppure devo sostituire manualmente su tutti e 35 i fogli la scritta 1^ con le giornate rispettive (es. 2^ - 3^ - 4^ etc )?
grazie e buona domenica a tutti
- Codice: Seleziona tutto
Sub ordinaeinvia()
'
' ordinaeinvia Macro
'
'
Range("A2:C27").Select
ActiveWorkbook.Worksheets("1^").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("1^").Sort.SortFields.Add Key:=Range("C3:C27"), _
SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"T,1 2 3 4 5 6 7", DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("1^").Sort
.SetRange Range("A2:C27")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("D2:F27").Select
ActiveWorkbook.Worksheets("1^").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("1^").Sort.SortFields.Add Key:=Range("F3:F27"), _
SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"T,1 2 3 4 5 6 7", DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("1^").Sort
.SetRange Range("D2:F27")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("G2:I27").Select
ActiveWorkbook.Worksheets("1^").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("1^").Sort.SortFields.Add Key:=Range("I3:I27"), _
SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"T,1 2 3 4 5 6 7", DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("1^").Sort
.SetRange Range("G2:I27")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("J2:L27").Select
ActiveWorkbook.Worksheets("1^").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("1^").Sort.SortFields.Add Key:=Range("L3:L27"), _
SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"T,1 2 3 4 5 6 7", DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("1^").Sort
.SetRange Range("J2:L27")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("M2:O27").Select
ActiveWorkbook.Worksheets("1^").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("1^").Sort.SortFields.Add Key:=Range("O3:O27"), _
SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"T,1 2 3 4 5 6 7", DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("1^").Sort
.SetRange Range("M2:O27")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=15
Range("A35:C60").Select
ActiveWorkbook.Worksheets("1^").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("1^").Sort.SortFields.Add Key:=Range("C36:C60"), _
SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"T,1 2 3 4 5 6 7", DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("1^").Sort
.SetRange Range("A35:C60")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("D35:F60").Select
ActiveWorkbook.Worksheets("1^").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("1^").Sort.SortFields.Add Key:=Range("F36:F60"), _
SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"T,1 2 3 4 5 6 7", DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("1^").Sort
.SetRange Range("D35:F60")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("G35:I60").Select
ActiveWorkbook.Worksheets("1^").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("1^").Sort.SortFields.Add Key:=Range("I36:I60"), _
SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"T,1 2 3 4 5 6 7", DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("1^").Sort
.SetRange Range("G35:I60")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("J35:L60").Select
ActiveWorkbook.Worksheets("1^").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("1^").Sort.SortFields.Add Key:=Range("L36:L60"), _
SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"T,1 2 3 4 5 6 7", DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("1^").Sort
.SetRange Range("J35:L60")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("M35:O60").Select
ActiveWorkbook.Worksheets("1^").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("1^").Sort.SortFields.Add Key:=Range("O36:O60"), _
SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"T,1 2 3 4 5 6 7", DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("1^").Sort
.SetRange Range("M35:O60")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=-33
Range("A3:B15").Select
Selection.Copy
Range("A3:B13").Select
Application.CutCopyMode = False
Selection.Copy
Range("X4").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A14:B20").Select
Application.CutCopyMode = False
Selection.Copy
Range("X18").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("D3:E13").Select
Application.CutCopyMode = False
Selection.Copy
Range("AC4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("D14:E20").Select
Application.CutCopyMode = False
Selection.Copy
Range("AC18").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G3:H13").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
Range("AH4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
Range("G14:H20").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
Range("AH18").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
Range("J3:K13").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
Range("AM4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
Range("J14:K20").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
Range("AM18").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
Range("M3:N13").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.LargeScroll ToRight:=1
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 38
Range("AR4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.LargeScroll ToRight:=-1
Range("M14:N20").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
Range("AR18").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll Down:=15
Range("A36:B46").Select
Application.CutCopyMode = False
Selection.Copy
Range("X34").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A47:B53").Select
Application.CutCopyMode = False
Selection.Copy
Range("X48").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("D36:E46").Select
Application.CutCopyMode = False
Selection.Copy
Range("AC34").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("D47:E53").Select
Application.CutCopyMode = False
Selection.Copy
Range("AC48").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G36:H46").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
Range("AH34").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollColumn = 7
Range("G47:H53").Select
Application.CutCopyMode = False
Selection.Copy
Range("AH48").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("J36:K46").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
Range("AM34").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
Range("J47:K53").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
Range("AM48").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
Range("M36:N46").Select
Range("N36").Activate
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
Range("AR34").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
Range("M47:N53").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 26
Range("AR48").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll Down:=-30
Application.CutCopyMode = False
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 1
Range("Q21").Select
End Sub