Valutazione 4.87/ 5 (100.00%) 5838 voti

Condividi:        

help formule e macro per programma fantacalcio

Vuoi potenziare i tuoi documenti Word? Non sai come si fa una macro in Excel? Devi creare una presentazione in PowerPoint?
Oppure sei passato a OpenOffice e non sei sicuro di come lavorare al meglio?

Moderatori: Anthony47, Flash30005

Re: help formule e macro per programma fantacalcio

Postdi gigilatrottola » 21/11/10 10:31

eccomi qua a dar fastidio di nuovo......
pensavo di aver risolto ma provando il file è uscito fuori un'altro problema :D

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
gigilatrottola
Utente Senior
 
Post: 184
Iscritto il: 10/06/06 09:58

Sponsor
 

Re: help formule e macro per programma fantacalcio

Postdi gigilatrottola » 21/11/10 10:35

ragazzi mi dovete scusare, mi sono dimenticato di mettere code nel primo messaggio, ora non riesco a cancellarlo.....c'è la possibilità di cancellarlo?
grazie e scusatemi di nuovo.
gigilatrottola
Utente Senior
 
Post: 184
Iscritto il: 10/06/06 09:58

Re: help formule e macro per programma fantacalcio

Postdi Flash30005 » 21/11/10 15:45

Puoi usare la funzione worksheets.count per sapere quanti fogli ci sono nella cartella e richiamare quello o quelli che ti interessano
oppure con un For ... next
tipo
Codice: Seleziona tutto
For FF = 1 to 4
Worksheets(FF & "^").range....
next FF


Consiglio sempre di evitare di mettere suffissi e stringhe non utili alle macro ma "piacevoli" a vedersi perché poi queste aggiunte costringono l'utente a richiamarle.
In questo caso il simbolo "^", se non ci fosse stato il foglio veniva richiamato con una semplice formula di questo tipo
Codice: Seleziona tutto
Worksheets(FF).range...


Fai sapere
Ciao
Flash
Win7 + Office 2010 Ita
"Fotografica" al servizio dell'immagine

Ottime opportunità di lavoro (part-time o full-time) con guadagni immediati. Info in MP
Avatar utente
Flash30005
Moderatore
 
Post: 8460
Iscritto il: 27/09/07 11:44
Località: Roma +o-

Re: help formule e macro per programma fantacalcio

Postdi gigilatrottola » 21/11/10 18:07

ciao flash,
ho seguito il tuo consiglio e ho tolto tutti i simboli "^" dai 36 foglio, cortesemente mi aiuti a fare questa macro?

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



mi trovo a dar fastidio, ora mi sono accorto che anche questa macro ha lo stesso problema di questa di sopra , e cioè:
Codice: Seleziona tutto
Sub inviarisultatiaclassifica()
'
' inviarisultatiaclassifica Macro
'

'
    Range("R2:U19").Select
    Selection.Copy
    Sheets("classifica").Select
    Range("B18:C18").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("G26").Select
    Application.CutCopyMode = False
    Range("F23").Select
    Sheets("1^").Select
    Range("R50").Select
    Sheets("classifica").Select
    Range("F23").Select
End Sub



mi dici come devo fare?

ps ho sostituito tutte i 36 fogli con "^" con semplici numeri da 1 a 36.

ciao e grazie ancora
gigilatrottola
Utente Senior
 
Post: 184
Iscritto il: 10/06/06 09:58

Re: help formule e macro per programma fantacalcio

Postdi Flash30005 » 21/11/10 22:59

Ok
ti chiedo, come la solito, di inviare il file con la descrizione di cò che vorresti ottenere perché ho dato uno sguardo all'ultimo file che ti ho inviato e, in quel file, ho solo un foglio con nome "Attuale", nel quale c'è la classifica quindi non so proprio cosa dovrei fare con i fogli da 1 a 36 che non ho.

Ciao
Flash
Win7 + Office 2010 Ita
"Fotografica" al servizio dell'immagine

Ottime opportunità di lavoro (part-time o full-time) con guadagni immediati. Info in MP
Avatar utente
Flash30005
Moderatore
 
Post: 8460
Iscritto il: 27/09/07 11:44
Località: Roma +o-

Re: help formule e macro per programma fantacalcio

Postdi gigilatrottola » 22/11/10 14:49

ecco il file......
http://rapidshare.com/files/432433773/p ... o_2010.xls


praticamente la prima macro ordina ed invia formazioni deve ordinare le sqaudre x T e poi x 1 2 3 4 5 6 e 7 (colonne C F I ect) e poi incollarle nell'altra parte del foglio.
es squadra 1
- ordinare colonna C3 - C27 prima per T poi per 1 2 3 4 5 6 7
- gli 11 con la T (A3 - B13) incollare in X4 - Y14
- poi le 7 riserve (A14 - B20) incollare in X18 - Y24,
e questo x tutte le 10 squadre x tutti i 36 fogli (dato che una volta sistemato il 1 foglio faccio il copia/incolla x tutti i restanti 35 fogli)

la seconda macro (invia risultati a classifica)
prende le celle R2 -U19 le copia e le incolla in classifica nelle celle B18 - E35.
spero di essere stato chiaro.....
ciao e grazie
gigilatrottola
Utente Senior
 
Post: 184
Iscritto il: 10/06/06 09:58

Re: help formule e macro per programma fantacalcio

Postdi Flash30005 » 23/11/10 08:36

Ho inserito il ciclo For next Per i 36 fogli e ripulito un po' la macro dagli inutili "Scroll" causati dalla registrazione.

Si potrebbe migliorare togliendo molti cipy e select come poi ti spiegherò intanto ecco la macro
Codice: Seleziona tutto
Sub ordinaeinvia()

For FF = 1 To 36

    Range("A2:C27").Select
    ActiveWorkbook.Worksheets(FF).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(FF).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(FF).Sort
        .SetRange Range("A2:C27")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("D2:F27").Select
    ActiveWorkbook.Worksheets(FF).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(FF).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(FF).Sort
        .SetRange Range("D2:F27")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("G2:I27").Select
    ActiveWorkbook.Worksheets(FF).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(FF).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(FF).Sort
        .SetRange Range("G2:I27")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("J2:L27").Select
    ActiveWorkbook.Worksheets(FF).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(FF).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(FF).Sort
        .SetRange Range("J2:L27")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("M2:O27").Select
    ActiveWorkbook.Worksheets(FF).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(FF).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(FF).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(FF).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(FF).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(FF).Sort
        .SetRange Range("A35:C60")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("D35:F60").Select
    ActiveWorkbook.Worksheets(FF).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(FF).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(FF).Sort
        .SetRange Range("D35:F60")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("G35:I60").Select
    ActiveWorkbook.Worksheets(FF).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(FF).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(FF).Sort
        .SetRange Range("G35:I60")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("J35:L60").Select
    ActiveWorkbook.Worksheets(FF).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(FF).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(FF).Sort
        .SetRange Range("J35:L60")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("M35:O60").Select
    ActiveWorkbook.Worksheets(FF).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(FF).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(FF).Sort
        .SetRange Range("M35:O60")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    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
    Range("AH4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("G14:H20").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AH18").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("J3:K13").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AM4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("J14:K20").Select
    Application.CutCopyMode = False
    Selection.Copy
      Range("AM18").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("M3:N13").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.LargeScroll ToRight:=1
    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
     Range("AR18").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    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
    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
    Range("AM34").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("J47:K53").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AM48").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("M36:N46").Select
    Range("N36").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Range("AR34").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
      Range("M47:N53").Select
    Application.CutCopyMode = False
    Range("AR48").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    Range("Q21").Select
   
    Next FF
       
End Sub



(Premetto che non entro in merito a cosa fa la macro ma ti dò qualche consiglio per ottimizzarla)

Prendiamo in esame un passaggio di copia e incolla

Codice: Seleziona tutto
   Range("J47:K53").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AM48").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


Qui in effetti copi il range J47:K57 e lo incolli in AM48

se devi fare un semplice copia e incolla puoi utilizzare una unica riga di codice senza la select della cella destinazione in questa maniera
Codice: Seleziona tutto
Range("J47:K53").Copy Destination:=Range("AM48")

ma se devi copiare solo i valori e non il formato (come vedo tu faccia dal tuo codice allora devi adottare questo altro codice
Codice: Seleziona tutto
Range("AM48").Value = Range("J47:K53").Value

Fai attenzione! In tutti e due i casi, inserendo Range("AM48") senza specificare il foglio verrà presa in esame la cella del foglio attivo in quel momento
quindi se nella routine c'è una precedente selezione al foglio corretto (tipo Worksheets("X").select) non hai alcun problema ma se non vuoi selezionare il foglio devi in ogni codice indicare anche il foglio origine dati e destinazione quindi, il codice precedente diverrà
Codice: Seleziona tutto
Worksheets("X").Range("AM48").Value = Worksheets("X").Range("J47:K53").Value

e nella macro postata su sarà quindi

Codice: Seleziona tutto
Worksheets(FF).Range("AM48").Value = Worksheets(FF).Range("J47:K53").Value


Fai sapere se tutto ok

ciao
Flash
Win7 + Office 2010 Ita
"Fotografica" al servizio dell'immagine

Ottime opportunità di lavoro (part-time o full-time) con guadagni immediati. Info in MP
Avatar utente
Flash30005
Moderatore
 
Post: 8460
Iscritto il: 27/09/07 11:44
Località: Roma +o-

Re: help formule e macro per programma fantacalcio

Postdi gigilatrottola » 24/11/10 20:43

ciao flash, ho riscontrato 2 problemi.....
il primo:
ho provato la prima macro fatta, eseguendola mi ha dato un errore, vedi sta alla fine di questa macro evidenziata,


Codice: Seleziona tutto
 Sub ordinaeinvia()

    For FF = 1 To 36

        Range("A2:C27").Select
        ActiveWorkbook.Worksheets(FF).Sort.SortFields.Clear
        ActiveWorkbook.Worksheets(FF).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(FF).Sort
            .SetRange Range("A2:C27")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Range("D2:F27").Select
        ActiveWorkbook.Worksheets(FF).Sort.SortFields.Clear
        ActiveWorkbook.Worksheets(FF).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(FF).Sort
            .SetRange Range("D2:F27")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Range("G2:I27").Select
        ActiveWorkbook.Worksheets(FF).Sort.SortFields.Clear
        ActiveWorkbook.Worksheets(FF).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(FF).Sort
            .SetRange Range("G2:I27")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Range("J2:L27").Select
        ActiveWorkbook.Worksheets(FF).Sort.SortFields.Clear
        ActiveWorkbook.Worksheets(FF).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(FF).Sort
            .SetRange Range("J2:L27")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Range("M2:O27").Select
        ActiveWorkbook.Worksheets(FF).Sort.SortFields.Clear
        ActiveWorkbook.Worksheets(FF).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(FF).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(FF).Sort.SortFields.Clear
        ActiveWorkbook.Worksheets(FF).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(FF).Sort
            .SetRange Range("A35:C60")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Range("D35:F60").Select
        ActiveWorkbook.Worksheets(FF).Sort.SortFields.Clear
        ActiveWorkbook.Worksheets(FF).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(FF).Sort
            .SetRange Range("D35:F60")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Range("G35:I60").Select
        ActiveWorkbook.Worksheets(FF).Sort.SortFields.Clear
        ActiveWorkbook.Worksheets(FF).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(FF).Sort
            .SetRange Range("G35:I60")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Range("J35:L60").Select
        ActiveWorkbook.Worksheets(FF).Sort.SortFields.Clear
        ActiveWorkbook.Worksheets(FF).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(FF).Sort
            .SetRange Range("J35:L60")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Range("M35:O60").Select
        ActiveWorkbook.Worksheets(FF).Sort.SortFields.Clear
        ActiveWorkbook.Worksheets(FF).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(FF).Sort
            .SetRange Range("M35:O60")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        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
        Range("AH4").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("G14:H20").Select
        Application.CutCopyMode = False
        Selection.Copy
        Range("AH18").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("J3:K13").Select
        Application.CutCopyMode = False
        Selection.Copy
        Range("AM4").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("J14:K20").Select
        Application.CutCopyMode = False
        Selection.Copy
          Range("AM18").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("M3:N13").Select
        Application.CutCopyMode = False
        Selection.Copy
        ActiveWindow.LargeScroll ToRight:=1
        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
         Range("AR18").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        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
        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
        Range("AM34").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("J47:K53").Select
        Application.CutCopyMode = False
        Selection.Copy
        Range("AM48").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("M36:N46").Select
        Application.CutCopyMode = False
        Selection.Copy
        Range("AR34").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
          Range("M47:N53").Select
        Application.CutCopyMode = False
        Range("AR48").Select
       [b][color=#4000FF] Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False[/color][/b]
        Application.CutCopyMode = False

        Range("Q21").Select
       
        Next FF
           
    End Sub



il secondo:
ho provato a modificare la macro come hai detto ma alla fine la macro non da i risultati voluti , sicuramente l'ho modificata in modo errato.

questa la macro:
Codice: Seleziona tutto

Sub ordinaeinvia()

    For FF = 1 To 36

        Range("A2:C27").Select
        ActiveWorkbook.Worksheets(FF).Sort.SortFields.Clear
        ActiveWorkbook.Worksheets(FF).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(FF).Sort
            .SetRange Range("A2:C27")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Range("D2:F27").Select
        ActiveWorkbook.Worksheets(FF).Sort.SortFields.Clear
        ActiveWorkbook.Worksheets(FF).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(FF).Sort
            .SetRange Range("D2:F27")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Range("G2:I27").Select
        ActiveWorkbook.Worksheets(FF).Sort.SortFields.Clear
        ActiveWorkbook.Worksheets(FF).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(FF).Sort
            .SetRange Range("G2:I27")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Range("J2:L27").Select
        ActiveWorkbook.Worksheets(FF).Sort.SortFields.Clear
        ActiveWorkbook.Worksheets(FF).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(FF).Sort
            .SetRange Range("J2:L27")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Range("M2:O27").Select
        ActiveWorkbook.Worksheets(FF).Sort.SortFields.Clear
        ActiveWorkbook.Worksheets(FF).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(FF).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(FF).Sort.SortFields.Clear
        ActiveWorkbook.Worksheets(FF).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(FF).Sort
            .SetRange Range("A35:C60")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Range("D35:F60").Select
        ActiveWorkbook.Worksheets(FF).Sort.SortFields.Clear
        ActiveWorkbook.Worksheets(FF).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(FF).Sort
            .SetRange Range("D35:F60")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Range("G35:I60").Select
        ActiveWorkbook.Worksheets(FF).Sort.SortFields.Clear
        ActiveWorkbook.Worksheets(FF).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(FF).Sort
            .SetRange Range("G35:I60")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Range("J35:L60").Select
        ActiveWorkbook.Worksheets(FF).Sort.SortFields.Clear
        ActiveWorkbook.Worksheets(FF).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(FF).Sort
            .SetRange Range("J35:L60")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Range("M35:O60").Select
        ActiveWorkbook.Worksheets(FF).Sort.SortFields.Clear
        ActiveWorkbook.Worksheets(FF).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(FF).Sort
            .SetRange Range("M35:O60")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Worksheets(FF).Range("X4").Value = Worksheets(FF).Range("A3:B13").Value
        Worksheets(FF).Range("X18").Value = Worksheets(FF).Range("A14:B20").Value
        Worksheets(FF).Range("AC4").Value = Worksheets(FF).Range("D3:E13").Value
        Worksheets(FF).Range("AC18").Value = Worksheets(FF).Range("D14:E20").Value
        Worksheets(FF).Range("AH4").Value = Worksheets(FF).Range("G3:H13").Value
        Worksheets(FF).Range("AH18").Value = Worksheets(FF).Range("G14:H20").Value
        Worksheets(FF).Range("AM4").Value = Worksheets(FF).Range("J3:K13").Value
        Worksheets(FF).Range("AM18").Value = Worksheets(FF).Range("J14:K20").Value
        Worksheets(FF).Range("AR4").Value = Worksheets(FF).Range("M3:N13").Value
        Worksheets(FF).Range("AR18").Value = Worksheets(FF).Range("M14:N20").Value
        Worksheets(FF).Range("X34").Value = Worksheets(FF).Range("A36:B46").Value
        Worksheets(FF).Range("X48").Value = Worksheets(FF).Range("A47:B53").Value
        Worksheets(FF).Range("AC34").Value = Worksheets(FF).Range("D36:E46").Value
        Worksheets(FF).Range("AC48").Value = Worksheets(FF).Range("D47:E53").Value
        Worksheets(FF).Range("AH34").Value = Worksheets(FF).Range("G36:H46").Value
        Worksheets(FF).Range("AH48").Value = Worksheets(FF).Range("G47:H53").Value
        Worksheets(FF).Range("AM34").Value = Worksheets(FF).Range("J36:K46").Value
        Worksheets(FF).Range("AM48").Value = Worksheets(FF).Range("J47:K53").Value
        Worksheets(FF).Range("AR34").Value = Worksheets(FF).Range("M36:N46").Value
        Worksheets(FF).Range("AR48").Value = Worksheets(FF).Range("M47:N53").Value
        Range("Q21").Select
        Next FF
    End Sub



ultima cosa: dato che avevo anche questa macro
Codice: Seleziona tutto
    Sub inviarisultatiaclassifica()
    '
    ' inviarisultatiaclassifica Macro
    '

    '
        Range("R2:U19").Select
        Selection.Copy
        Sheets("classifica").Select
        Range("B18:C18").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        Range("G26").Select
        Application.CutCopyMode = False
        Range("F23").Select
        Sheets("1^").Select
        Range("R50").Select
        Sheets("classifica").Select
        Range("F23").Select
    End Sub



nella riga Sheets("1^").Select al posto di 1^ metto FF?

ciao e grazie
gigilatrottola
Utente Senior
 
Post: 184
Iscritto il: 10/06/06 09:58

Re: help formule e macro per programma fantacalcio

Postdi gigilatrottola » 24/11/10 20:48

pensavo che evidenziava la macro , ma forse con il code nn funziona , comunque alla fine della macro da quest'errore:
Range("M47:N53").Select
Application.CutCopyMode = False
Range("AR48").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False

Range("Q21").Select

Next FF

End Sub


mi sono dimenticato di dirti che serve che mi incolli i valori non fare il copia/incolla normale.

di nuovo grazie e buona serata
gigilatrottola
Utente Senior
 
Post: 184
Iscritto il: 10/06/06 09:58

Re: help formule e macro per programma fantacalcio

Postdi Flash30005 » 24/11/10 22:36

Ho di nuovo scaricato il tuo file
ho sostituito i simboli "^" per coerenza con i fogli
e senza inserire il ciclo For... Next
ho avviato la macro ma non funziona
quindi se non funziona la tua macro originale non potrà mai funzionare la macro con il ciclo For... next

ora dovrei studiare passo passo cosa vorresti ottenere, con la tua macro, ma penso che questo sia più semplice per te

Poi inserire il cilco For... next, come avrai visto, è abbastanza semplice

E' logico che se inserisci il Ciclo For... next Con FF che va da 1 a 36 tutte le righe della macro che riportano
Worksheets("1^")....
devono essere modificate in
Worksheets(FF)....

Fai sapere
Ciao
Flash
Win7 + Office 2010 Ita
"Fotografica" al servizio dell'immagine

Ottime opportunità di lavoro (part-time o full-time) con guadagni immediati. Info in MP
Avatar utente
Flash30005
Moderatore
 
Post: 8460
Iscritto il: 27/09/07 11:44
Località: Roma +o-

Re: help formule e macro per programma fantacalcio

Postdi Anthony47 » 24/11/10 22:40

Secondo me si arriva all' istruzione "PasteSpecial" che va in errore senza niente copiato in memoria: o c' e' un Application.CutCopyMode = False di troppo oppure un Selection.Copy in meno; propendo per la seconda, ma voi sapete meglio di me che cosa dovrebbe fare la macro in quel frangente.

Ciao
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13885
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: help formule e macro per programma fantacalcio

Postdi gigilatrottola » 24/11/10 23:27

eccomi qua di nuovo....
allora controllando passo passo tutte le righe infatti c'era ad un punto della macro che mancava un Selection.Copy e poi c'era qualche scroll in pià

ora però è nato un'altro problema , e cioè eseguendo la macro, continua all'infinito e quando premo esc per fermarla mi da il debug ed aprendo la macro mi seleziona di giallo su apply

Range("A2:C27").Select
ActiveWorkbook.Worksheets(FF).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(FF).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(FF).Sort
.SetRange Range("A2:C27")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

cosa posso fare?
graze
gigilatrottola
Utente Senior
 
Post: 184
Iscritto il: 10/06/06 09:58

Re: help formule e macro per programma fantacalcio

Postdi gigilatrottola » 25/11/10 19:12

ciao ragazzi allora ricapitolando un pochino......

ho rifatto la macro e questa funziona perfettamente sul foglio 1 ma sul resto dei fogli non funziona
questa è la macro:
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.ScrollRow = 14
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    Range("A3:B13").Select
    Selection.Copy
    Range("X4").Select
    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
    Range("AH4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("G14:H20").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AH18").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("J3:K13").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    Range("AM4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.ScrollColumn = 12
    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 = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 15
    Range("AM18").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    Range("M3:N13").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 23
    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 = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    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
    Range("AR18").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 20
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 26
    ActiveWindow.ScrollRow = 29
    ActiveWindow.ScrollRow = 31
    ActiveWindow.ScrollRow = 34
    ActiveWindow.ScrollRow = 36
    ActiveWindow.ScrollRow = 37
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 40
    ActiveWindow.ScrollRow = 37
    ActiveWindow.ScrollRow = 36
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 34
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 32
    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
    Range("AH34").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    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 = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 14
    Range("AM34").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    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 = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 15
    Range("AM48").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    Range("M36:N46").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 16
    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 = 19
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    Range("M47:N53").Select
    Application.CutCopyMode = False
    Selection.Copy
    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
    Range("AR48").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=-39
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("Q22").Select
    Application.CutCopyMode = False
    Range("Q22").Select
End Sub



ho modificato questa macro mettendo al posto di "1" FF ed ho inserito le scritte
"For FF = 1 To 36" all'inizio della macro e "Next FF" alla fine ma eseguendo la macro mi da un debug e mi segnala di giallo questa riga:
"ActiveWorkbook.Worksheets("FF").Sort.SortFields.Clear" e di conseguenza non mi fa andare avanti.

così ho copiato la parte iniziale della macro di flash e l'ho sosituita alla mia (solo la parte che mette in ordine di T e 1 2 3 4 5 6 7) ed eseguendola la macro funziona all'infinit finchè non premo esc sulla tastiera e mi esce un debug segnalandomi di giallo questa riga:
".Apply"

ora nn so dove sbaglio oppure c'è qualcosa di fondo di sbagliato nella macro.....
come sempre vi ringrazio anticipatamente.......
gigilatrottola
Utente Senior
 
Post: 184
Iscritto il: 10/06/06 09:58

Re: help formule e macro per programma fantacalcio

Postdi Anthony47 » 26/11/10 00:13

Purtroppo quello che scrivi non e' sufficiente a capire che cosa genera il loop; dopo che se andato in debug quindi esegui la macro step by step (tramite F8) e segui il flusso; verificherai direttamente dove e' l' eventuale errore. Puo' essere utile anche leggere il valore FF, e per questo bastera' posizionare il cursore sopra la scritta FF (no click) e dopo quanche secondo ti sara' visualizzato il valore.
E se non riesci a risolvere posta ancora indicando che cosa hai scoperto.

Ciao
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13885
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: help formule e macro per programma fantacalcio

Postdi Flash30005 » 26/11/10 01:26

(Leggi il post di Anthony qui sopra)

Ho provato la tua macro ma si blocca alla seconda riga di codice
Codice: Seleziona tutto
    ActiveWorkbook.Worksheets("1").Sort.SortFields.Clear

Errore runtime "438"

Evidentemente quando l'hai creata ti eri posizionato su un deteminato foglio che dovrei ricercare mentre è più semplice (serve anche a te ad imparare) se quando inizi una registrazione non ti posionassi sul foglio di effettivo intervento, mi spiego:
devi effettuare delle azioni sul foglio1?
posizionati (prima di iniziare la registrazione) su un qualsiasi altro foglio (ma non il foglio1), avvii la registrazione,
ti porti sul foglio1 e fai i passi che ti occorrono fino al completamento.
Una volta fermata la registrazione
vai nel codice vba e cancelli tutte le righe codice relative allo scroll del mouse
che riconoscerai...
Codice: Seleziona tutto
ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 13
   etc
etc


(Come noterai ne hai molte)
Dopo averle cancellate
(attento! perché tra una serie e l'altra ci sono delle righe che non devono essere cancellate tipo:
Codice: Seleziona tutto
     ActiveWindow.ScrollColumn = 5        '<<<< cancellare
    Range("M3:N13").Select                '<<< non cancellare
    Application.CutCopyMode = False   '<<< non cancellare
    Selection.Copy                             '<<< non cancellare
    ActiveWindow.ScrollColumn = 7    '<<<< cancellare
)
riprovi il funzionamento della macro
avviandola avendo attivo un foglio diverso da quello di intervento
se tutto funziona riposta la macro.

Ciao
Flash
Win7 + Office 2010 Ita
"Fotografica" al servizio dell'immagine

Ottime opportunità di lavoro (part-time o full-time) con guadagni immediati. Info in MP
Avatar utente
Flash30005
Moderatore
 
Post: 8460
Iscritto il: 27/09/07 11:44
Località: Roma +o-

Re: help formule e macro per programma fantacalcio

Postdi gigilatrottola » 26/11/10 10:50

ok ragazzi
grazie ancora della vostra pazienza....
oggi pomeriggio provero' le vostre indicazioni e vi faro' sapere......

grazie..................
gigilatrottola
Utente Senior
 
Post: 184
Iscritto il: 10/06/06 09:58

Re: help formule e macro per programma fantacalcio

Postdi gigilatrottola » 26/11/10 16:43

x flash
ho rifatto tutto quello che hai detto è questa è la macro perfettamente funzionante su foglio 1, nel caso vado a provare la macro com'è questa di sotto su foglio 2 logicamente mi riposta su foglio 1 e prosegue con la macro.
ho tolto la scritta Sheets("1").Select dalla macro e l'ho riavviata su foglio 1 e funziona , ho avviato la macro su foglio 2 e praticamente non mette in ordine per T e poi 1 2 3 4 5 6 7 e in più cosa molto strana e che ho notato solo ora, se vado su foglio 1 mi tiene evidenziato le celle che vanno da M36 a O60.


Codice: Seleziona tutto
Sub ordinaeinvia()
'
' ordinaeinvia Macro
'

'
    Sheets("1").Select
    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
    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
    Range("A3:B13").Select
    Selection.Copy
    Range("X4").Select
    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
    Range("AH4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("G14:H20").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AH18").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("J3:K13").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AM4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("J14:K20").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AM18").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("M3:N13").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AR4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("M14:N20").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AR18").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    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
    Range("AH34").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    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
    Range("AM34").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("J47:K53").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AM48").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("M36:N46").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AR34").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("M47:N53").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AR48").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("Q22").Select
End Sub



per anthony.

ho provato ad avviare la macro passo passo e mi si blocca su questa riga
ActiveWorkbook.Worksheets("FF").Sort.SortFields.Clear
e mi tiene evidenziato le celle A2 C27.
ho provato a vedere il valore di FF e mi dice che è uguale a 1
FF=1



volevo domandare un'altra cosa....meno importante di questa macro ma che non so nemmeno se possibile risolvere.....

molte volte nella composizione della panchina non tutti impiegano le 7 sostituzioni e cosi facendo metto i numeri da 1 a 5 per esempio. di conseguenza quando poi vado a lanciare la macro che prevede di copiare ed incollare (per esempio) le celle che vanno da A14 a B20 nelle celle X18 Y24 mi incolla anche giocatori che non risultano in panchina.
voi come soluzione che mi consigliate?
quella di eliminare manualmente i giocatori superflui o quella di creare spazi vuoti da aggiungere ale formazioni dove mettere i numeri rimanenti (esempio 5 6 7 )?


grazie 1000
gigilatrottola
Utente Senior
 
Post: 184
Iscritto il: 10/06/06 09:58

Re: help formule e macro per programma fantacalcio

Postdi Anthony47 » 26/11/10 23:59

Mi dici per favore quale e' la macro completa, quale errore ti da', su quale riga e quale versione di excel e' in uso?

Ciao
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13885
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: help formule e macro per programma fantacalcio

Postdi gigilatrottola » 27/11/10 00:09

ciao anthony,
la macro completa e funzionante è l'ultima postata, ma funziona solo sul foglio 1 mentre sui restanti 35 fogli non funziona. mentre se applico il ciclo For... Next mi si blocca subito alla seconda riga e cioè:
ActiveWorkbook.Worksheets("1").Sort.SortFields.Clear
logicamente con FF al posto di 1
io uso excel 2010.
grazie
gigilatrottola
Utente Senior
 
Post: 184
Iscritto il: 10/06/06 09:58

Re: help formule e macro per programma fantacalcio

Postdi Anthony47 » 27/11/10 00:15

"Quale errore"?
La macro e' quella del 24/11/10 20:43? La prima o la seconda?

Ciao
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13885
Iscritto il: 21/03/06 16:03
Località: Ivrea

PrecedenteProssimo

Torna a Applicazioni Office Windows


Topic correlati a "help formule e macro per programma fantacalcio":


Chi c’è in linea

Visitano il forum: Nessuno e 14 ospiti