Nettoyage de macro

patbeu · 27 Février 2009

Hello, j'aurai besoin d'un peu d'aide ou me dire ce que je dois faire pour pouvoir "alleger" ma macro,

Code:

Sub Copie_Menu_x70()
'
' Copie_Menu Macro
' Macro enregistrée par Patbeu
'

'
    Application.ScreenUpdating = False
    
    Sheets("E1").Select
    Sheets(Array("E1", "E2", "E3", "E4", "E5", "E6", "E7", "E8", "E9", "E10", "E11", "E12", _
        "E13", "E14", "E15", "E16", "E17")) _
        .Select
    Sheets("E1").Activate
    Sheets(Array("E18", "E19", "E20", "E21", "E22", "E23", "E24", "E25", "E26", "E27", "E28", "E29", "E30", "E31", "E32", "E33", "E34", "E35", "E36", _
        "E37", "E38", "E39", "E40", "E41", "E42", "E43", "E44", "E45", "E46", "E47", "E48", "E49", _
        "E50")).Select Replace:=False
    Sheets(Array("E51", "E52", "E53", "E54", "E55", "E56", "E57", "E58", "E59", "E60", "E61", _
        "E62", "E63", "E64", "E65", "E66", "E67", "E68", "E69", "E70")).Select Replace:=False
    
'ligne entete menu
    Range("X162:X168").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-160]C[-22]"
    Range("X145:X156").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-143]C[-10]"
    Range("X139:X143").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-137]C[3]"
    
'code client
    
'ligne adresse
    Range("Z140:Z141").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-136]C[3]"
    Range("Z145:Z146").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-141]C[-2]"
    Range("Z157").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-153]C[-13]"
    Range("Z168").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-164]C[-24]"
    
    Range("AB168").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-162]C[-26]"
    Range("AB164").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-158]C[-22]"
    Range("AB156:AB157").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-150]C[-15]"
    Range("AB153").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-147]C[-11]"
    Range("AB146").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-140]C[-4]"
    Range("AB140:AB141").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-134]C[1]"
    
    Range("AD140:AD141").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-132]C[-1]"
    Range("AD146").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-138]C[-6]"
    Range("AD148:AD165").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-140]C[-25]"
    Range("AD166:AD168").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-158]C[-28]"
'lignes date heure
    Range("AF160:AF167").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-150]C[-29]"
    Range("AH160:AH167").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-148]C[-31]"
    Range("AF157:AF158").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-147]C[-20]"
    Range("AH157:AH158").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-145]C[-22]"
    Range("AF156").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-146]C[-18]"
    Range("AH156").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-144]C[-20]"
    Range("AF152:AF153").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-142]C[-15]"
    Range("AH152:AH153").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-140]C[-17]"
    Range("AF151").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-141]C[-13]"
    Range("AH151").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-139]C[-15]"
    Range("AF147:AF148").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-137]C[-10]"
    Range("AH147:AH148").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-135]C[-12]"
    Range("AF146").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-136]C[-8]"
    Range("AH146").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-134]C[-10]"
    Range("AF142:AF143").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-132]C[-5]"
    Range("AH142:AH143").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-130]C[-7]"
    Range("AF141").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-131]C[-3]"
    Range("AH141").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-129]C[-5]"
    Range("AF137:AF138").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-127]C"
    Range("AH137:AH138").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-125]C[-2]"
    Range("AF136").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-126]C[2]"
    Range("AH136").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-124]C"
'ligne info commentaire
    Range("AI136:AI168").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-123]C[-32]"
    Range("AK136:AK168").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-121]C[-35]"

'ligne titre eclipse
    Range("AL168").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-152]C[-36]"
    Range("AL162:AL167").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-146]C[-35]"
'colone 1 eclipse
'colone 2 eclipse
'colone 3 eclipse
    Range("AM163:AM166").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-146]C[-35]"
    Range("AN163:AN166").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-145]C[-36]"
    Range("AO163:AO166").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-144]C[-37]"
    Range("AP163:AP166").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-143]C[-38]"
    Range("AQ163:AQ166").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-142]C[-39]"
'colone 4 eclipse
    Range("AM162").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-145]C[-31]"
    Range("AN162").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-144]C[-32]"
    Range("AO162").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-143]C[-33]"
    Range("AP162").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-142]C[-34]"
    Range("AQ162").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-141]C[-35]"
'ligne prix eclipse
    Range("AR168").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-146]C[-42]"
    Range("AR164:AR167").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-142]C[-41]"
        ' Range("AR162:AR163").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-140]C[-37]"

'ligne titre divers
    Range("AS168").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-145]C[-43]"
    Range("AS162:AS167").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-139]C[-42]"
'colone 1 divers
'colone 2 divers
'colone 3 divers
    Range("AT163:AT166").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-139]C[-42]"
    Range("AU163:AU166").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-138]C[-43]"
    Range("AV163:AV166").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-137]C[-44]"
    Range("AW163:AW166").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-136]C[-45]"
    Range("AX163:AX166").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-135]C[-46]"
        ' Range("AY163:AY166").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-134]C[-47]"
        ' Range("AZ163:AZ166").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-133]C[-48]"
'colone 4 divers
    Range("AT162").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-138]C[-38]"
    Range("AU162").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-137]C[-39]"
    Range("AV162").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-136]C[-40]"
    Range("AW162").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-135]C[-41]"
    Range("AX162").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-134]C[-42]"
        ' Range("AY162").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-133]C[-43]"
        ' Range("AZ162").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-132]C[-44]"
'ligne prix divers
    Range("BA168").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-137]C[-51]"
    Range("BA164:BA167").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-133]C[-50]"
        ' Range("BA162:BA163").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-131]C[-46]"
    
'ligne titre isopure
    Range("BB168").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-136]C[-52]"
    Range("BB162:BB167").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-130]C[-51]"
'colone 1 isopure
'colone 2 isopure
'colone 3 isopure
    Range("BC163:BC166").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-130]C[-51]"
    Range("BD163:BD166").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-129]C[-52]"
    Range("BE163:BE166").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-128]C[-53]"
'colone 4 isopure
    Range("BC162").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-129]C[-47]"
    Range("BD162").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-128]C[-48]"
    Range("BE162").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-127]C[-49]"
'ligne prix isopure
    Range("BF168").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-132]C[-56]"
    Range("BF164:BF167").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-128]C[-55]"
        ' Range("BF162:BF163").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-126]C[-51]"
    
'ligne titre poudre
    Range("BG168").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-131]C[-57]"
    Range("BG162:BG167").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-125]C[-56]"
'colone 1 poudre
'colone 2 poudre
    Range("BH167").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-129]C[-57]"
    Range("BI167").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-128]C[-58]"
    Range("BJ167").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-127]C[-59]"
    Range("BK167").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-126]C[-60]"
    Range("BL167").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-125]C[-61]"
    Range("BM167").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-124]C[-62]"
    Range("BN167").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-123]C[-63]"
    Range("BO167").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-122]C[-64]"
'colone 3 poudre
    Range("BH163:BH166").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-125]C[-56]"
    Range("BI163:BI166").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-124]C[-57]"
    Range("BJ163:BJ166").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-123]C[-58]"
    Range("BK163:BK166").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-122]C[-59]"
    Range("BL163:BL166").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-121]C[-60]"
    Range("BM163:BM166").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-120]C[-61]"
    Range("BN163:BN166").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-119]C[-62]"
    Range("BO163:BO166").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-118]C[-63]"
'colone 4 poudre
    Range("BH162").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-124]C[-52]"
    Range("BI162").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-123]C[-53]"
    Range("BJ162").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-122]C[-54]"
    Range("BK162").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-121]C[-55]"
    Range("BL162").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-120]C[-56]"
    Range("BM162").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-119]C[-57]"
    Range("BN162").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-118]C[-58]"
    Range("BO162").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-117]C[-59]"
'ligne prix poudre
    Range("BP168").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-122]C[-66]"
    Range("BP164:BP167").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-118]C[-65]"
        ' Range("BP162:BP163").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-116]C[-61]"
    
'ligne titre barre choco
    Range("BQ168").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-121]C[-67]"
    Range("BQ162:BQ167").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-115]C[-66]"
'colone 1 barre choco
'colone 2 barre choco
    Range("BR167").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-119]C[-67]"
    Range("BS167").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-118]C[-68]"
    Range("BT167").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-117]C[-69]"
    Range("BU167").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-116]C[-70]"
    Range("BV167").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-115]C[-71]"
        ' Range("BW167").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-114]C[-72]"
    Range("BX167").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-113]C[-73]"
        ' Range("BY167").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-112]C[-74]"
        ' Range("BZ167").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-111]C[-75]"
        ' Range("CA167").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-110]C[-76]"
'colone 3 barre choco
    Range("BR163:BR166").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-115]C[-66]"
    Range("BS163:BS166").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-114]C[-67]"
    Range("BT163:BT166").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-113]C[-68]"
    Range("BU163:BU166").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-112]C[-69]"
    Range("BV163:BV166").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-111]C[-70]"
        ' Range("BW163:BW166").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-110]C[-71]"
    Range("BX163:BX166").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-109]C[-72]"
        ' Range("BY163:BY166").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-108]C[-73]"
        ' Range("BZ163:BZ166").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-107]C[-74]"
        ' Range("CA163:CA166").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-106]C[-75]"
'colone 4 barre choco
    Range("BR162").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-114]C[-62]"
    Range("BS162").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-113]C[-63]"
    Range("BT162").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-112]C[-64]"
    Range("BU162").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-111]C[-65]"
    Range("BV162").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-110]C[-66]"
        ' Range("BW162").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-109]C[-67]"
    Range("BX162").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-108]C[-68]"
        ' Range("BY162").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-107]C[-69]"
        ' Range("BZ162").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-106]C[-70]"
        ' Range("CA162").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-105]C[-71]"
'ligne prix barre choco
    Range("CB168").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-110]C[-78]"
    Range("CB164:CB167").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-106]C[-77]"
        ' Range("CB162:CB163").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-104]C[-73]"
        
'ligne titre saucisson
    Range("AL160").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-144]C[-28]"
    Range("AL153:AL159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-137]C[-27]"
'colone 1 saucisson
'colone 2 saucisson
    Range("AM159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-142]C[-28]"
    Range("AN159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-141]C[-29]"
    Range("AO159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-140]C[-30]"
        ' Range("AP159").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-139]C[-31]"
'colone 3 saucisson
    Range("AM154:AM158").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-137]C[-27]"
    Range("AN154:AN158").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-136]C[-28]"
    Range("AO154:AO158").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-135]C[-29]"
        ' Range("AP154:AP158").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-134]C[-30]"
'colone 4 saucisson
    Range("AM153").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-136]C[-22]"
    Range("AN153").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-135]C[-23]"
    Range("AO153").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-134]C[-24]"
        ' Range("AP153").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-133]C[-25]"
'ligne prix saucisson
    Range("AQ160").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-139]C[-33]"
    Range("AQ155:AQ159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-134]C[-32]"
        ' Range("AQ153:AQ154").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-132]C[-27]"
        
'ligne titre dessert
    Range("AR160").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-138]C[-34]"
    Range("AR153:AR159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-131]C[-33]"
'colone 1 dessert
'colone 2 dessert
    Range("AS159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-136]C[-34]"
    Range("AT159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-135]C[-35]"
    Range("AU159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-134]C[-36]"
    Range("AV159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-133]C[-37]"
    Range("AW159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-132]C[-38]"
    Range("AX159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-131]C[-39]"
    Range("AY159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-130]C[-40]"
    Range("AZ159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-129]C[-41]"
    Range("BA159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-128]C[-42]"
    Range("BB159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-127]C[-43]"
    Range("BC159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-126]C[-44]"
    Range("BD159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-125]C[-45]"
    Range("BE159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-124]C[-46]"
    Range("BF159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-123]C[-47]"
    Range("BG159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-122]C[-48]"
    Range("BH159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-121]C[-49]"
    Range("BI159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-120]C[-50]"
    Range("BJ159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-119]C[-51]"
        ' Range("BK159").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-118]C[-52]"
        ' Range("BL159").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-117]C[-53]"
'colone 3 dessert
    Range("AS154:AS158").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-131]C[-33]"
    Range("AT154:AT158").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-130]C[-34]"
    Range("AU154:AU158").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-129]C[-35]"
    Range("AV154:AV158").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-128]C[-36]"
    Range("AW154:AW158").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-127]C[-37]"
    Range("AX154:AX158").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-126]C[-38]"
    Range("AY154:AY158").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-125]C[-39]"
    Range("AZ154:AZ158").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-124]C[-40]"
    Range("BA154:BA158").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-123]C[-41]"
    Range("BB154:BB158").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-122]C[-42]"
    Range("BC154:BC158").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-121]C[-43]"
    Range("BD154:BD158").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-120]C[-44]"
    Range("BE154:BE158").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-119]C[-45]"
    Range("BF154:BF158").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-118]C[-46]"
    Range("BG154:BG158").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-117]C[-47]"
    Range("BH154:BH158").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-116]C[-48]"
    Range("BI154:BI158").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-115]C[-49]"
    Range("BJ154:BJ158").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-114]C[-50]"
        ' Range("BK154:BK158").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-113]C[-51]"
        ' Range("BL154:BL158").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-112]C[-52]"
'colone 4 dessert
    Range("AS153").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-130]C[-28]"
    Range("AT153").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-129]C[-29]"
    Range("AU153").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-128]C[-30]"
    Range("AV153").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-127]C[-31]"
    Range("AW153").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-126]C[-32]"
    Range("AX153").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-125]C[-33]"
    Range("AY153").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-124]C[-34]"
    Range("AZ153").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-123]C[-35]"
    Range("BA153").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-122]C[-36]"
    Range("BB153").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-121]C[-37]"
    Range("BC153").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-120]C[-38]"
    Range("BD153").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-119]C[-39]"
    Range("BE153").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-118]C[-40]"
    Range("BF153").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-117]C[-41]"
    Range("BG153").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-116]C[-42]"
    Range("BH153").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-115]C[-43]"
    Range("BI153").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-114]C[-44]"
    Range("BJ153").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-113]C[-45]"
        ' Range("BK153").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-112]C[-46]"
        ' Range("BL153").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-111]C[-47]"
'ligne prix dessert
    Range("BM160").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-117]C[-55]"
    Range("BM155:BM159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-112]C[-54]"
        ' Range("BM153:BM154").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-110]C[-49]"

'ligne titre soupe
    Range("BN160").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-116]C[-56]"
    Range("BN153:BN159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-109]C[-55]"
'colone 1 soupe
'colone 2 soupe
    Range("BO159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-114]C[-56]"
    Range("BP159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-113]C[-57]"
    Range("BQ159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-112]C[-58]"
    Range("BR159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-111]C[-59]"
    Range("BS159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-110]C[-60]"
    Range("BT159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-109]C[-61]"
    Range("BU159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-108]C[-62]"
    Range("BV159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-107]C[-63]"
    Range("BW159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-106]C[-64]"
    Range("BX159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-105]C[-65]"
    Range("BY159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-104]C[-66]"
        ' Range("BZ159").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-103]C[-67]"
        ' Range("CA159").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-102]C[-68]"
'colone 3 soupe
    Range("BO154:BO158").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-109]C[-55]"
    Range("BP154:BP158").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-108]C[-56]"
    Range("BQ154:BQ158").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-107]C[-57]"
    Range("BR154:BR158").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-106]C[-58]"
    Range("BS154:BS158").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-105]C[-59]"
    Range("BT154:BT158").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-104]C[-60]"
    Range("BU154:BU158").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-103]C[-61]"
    Range("BV154:BV158").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-102]C[-62]"
    Range("BW154:BW158").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-101]C[-63]"
    Range("BX154:BX158").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-100]C[-64]"
    Range("BY154:BY158").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-99]C[-65]"
        ' Range("BZ154:BZ158").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-98]C[-66]"
        ' Range("CA154:CA158").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-97]C[-67]"
'colone 4 soupe
    Range("BO153").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-108]C[-50]"
    Range("BP153").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-107]C[-51]"
    Range("BQ153").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-106]C[-52]"
    Range("BR153").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-105]C[-53]"
    Range("BS153").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-104]C[-54]"
    Range("BT153").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-103]C[-55]"
    Range("BU153").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-102]C[-56]"
    Range("BV153").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-101]C[-57]"
    Range("BW153").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-100]C[-58]"
    Range("BX153").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-99]C[-59]"
    Range("BY153").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-98]C[-60]"
        ' Range("BZ153").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-97]C[-61]"
        ' Range("CA153").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-96]C[-62]"
'ligne prix soupe
    Range("CB160").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-102]C[-70]"
    Range("CB155:CB159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-97]C[-69]"
        ' Range("CB153:CB154").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-95]C[-64]"
    
'ligne titre assiette froide
    Range("AL151").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-135]C[-19]"
    Range("AL136:AL150").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-120]C[-18]"
'colone 1 assiette froide
'colone 2 assiette froide
    Range("AM150").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-133]C[-19]"
    Range("AN150").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-132]C[-20]"
    Range("AO150").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-131]C[-21]"
    Range("AP150").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-130]C[-22]"
    Range("AQ150").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-129]C[-23]"
    Range("AR150").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-128]C[-24]"
    Range("AS150").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-127]C[-25]"
        ' Range("AT150").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-126]C[-26]"
    Range("AU150").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-125]C[-27]"
    Range("AV150").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-124]C[-28]"
    Range("AW150").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-123]C[-29]"
        ' Range("AX150").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-122]C[-30]"
'colone 3 assiette froide
    Range("AM137:AM149").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-120]C[-18]"
    Range("AN137:AN149").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-119]C[-19]"
    Range("AO137:AO149").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-118]C[-20]"
    Range("AP137:AP149").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-117]C[-21]"
    Range("AQ137:AQ149").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-116]C[-22]"
    Range("AR137:AR149").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-115]C[-23]"
    Range("AS137:AS149").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-114]C[-24]"
        ' Range("AT137:AT149").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-113]C[-25]"
    Range("AU137:AU149").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-112]C[-26]"
    Range("AV137:AV149").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-111]C[-27]"
    Range("AW137:AW149").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-110]C[-28]"
        ' Range("AX137:AX149").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-109]C[-29]"
'colone 4 assiette froide
    Range("AM136").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-119]C[-5]"
    Range("AN136").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-118]C[-6]"
    Range("AO136").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-117]C[-7]"
    Range("AP136").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-116]C[-8]"
    Range("AQ136").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-115]C[-9]"
    Range("AR136").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-114]C[-10]"
    Range("AS136").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-113]C[-11]"
        ' Range("AT136").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-112]C[-12]"
    Range("AU136").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-111]C[-13]"
    Range("AV136").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-110]C[-14]"
    Range("AW136").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-109]C[-15]"
        ' Range("AX136").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-108]C[-16]"
'ligne prix assiette froide
    Range("AY151").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-122]C[-32]"
    Range("AY138:AY150").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-109]C[-31]"
        ' Range("AY136:AY137").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-107]C[-18]"
    
'ligne titre plat surgele
    Range("AZ151").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-121]C[-33]"
    Range("AZ136:AZ150").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-106]C[-32]"
'colone 1 plat surgele
'colone 2 plat surgele
    Range("BA150").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-119]C[-33]"
    Range("BB150").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-118]C[-34]"
    Range("BC150").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-117]C[-35]"
    Range("BD150").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-116]C[-36]"
    Range("BE150").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-115]C[-37]"
    Range("BF150").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-114]C[-38]"
    Range("BG150").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-113]C[-39]"
        ' Range("BH150").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-112]C[-40]"
'colone 3 plat surgele
    Range("BA137:BA149").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-106]C[-32]"
    Range("BB137:BB149").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-105]C[-33]"
    Range("BC137:BC149").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-104]C[-34]"
    Range("BD137:BD149").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-103]C[-35]"
    Range("BE137:BE149").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-102]C[-36]"
    Range("BF137:BF149").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-101]C[-37]"
    Range("BG137:BG149").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-100]C[-38]"
        ' Range("BH137:BH149").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-99]C[-39]"
'colone 4 plat surgele
    Range("BA136").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-105]C[-19]"
    Range("BB136").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-104]C[-20]"
    Range("BC136").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-103]C[-21]"
    Range("BD136").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-102]C[-22]"
    Range("BE136").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-101]C[-23]"
    Range("BF136").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-100]C[-24]"
    Range("BG136").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-99]C[-25]"
        ' Range("BH136").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-98]C[-26]"
'ligne prix plat surgele
    Range("BI151").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-112]C[-42]"
    Range("BI138:BI150").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-99]C[-41]"
        ' Range("BI136:BI137").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-97]C[-28]"
    
'ligne titre plat supp
    Range("BJ151").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-111]C[-43]"
    Range("BJ136:BJ150").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-96]C[-42]"
'colone 1 plat supp
'colone 2 plat supp
        ' Range("BK150").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-109]C[-43]"
        ' Range("BL150").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-108]C[-44]"
        ' Range("BM150").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-107]C[-45]"
        ' Range("BN150").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-106]C[-46]"
        ' Range("BO150").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-105]C[-47]"
        ' Range("BP150").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-104]C[-48]"
        ' Range("BQ150").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-103]C[-49]"
'colone 3 plat supp
    Range("BK137:BK149").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-96]C[-42]"
        ' Range("BL137:BL149").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-95]C[-43]"
        ' Range("BM137:BM149").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-94]C[-44]"
        ' Range("BN137:BN149").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-93]C[-45]"
        ' Range("BO137:BO149").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-92]C[-46]"
        ' Range("BP137:BP149").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-91]C[-47]"
        ' Range("BQ137:BQ149").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-90]C[-48]"
'colone 4 plat supp
        ' Range("BK136").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-95]C[-29]"
        ' Range("BL136").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-94]C[-30]"
        ' Range("BM136").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-93]C[-31]"
        ' Range("BN136").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-92]C[-32]"
        ' Range("BO136").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-91]C[-33]"
        ' Range("BP136").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-90]C[-34]"
        ' Range("BQ136").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-89]C[-35]"
'ligne prix plat supp
    Range("BR151").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-103]C[-51]"
    Range("BR138:BR150").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-90]C[-50]"
        ' Range("BR136:BR137").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-88]C[-37]"
    
'ligne titre pizza
    Range("BS151").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-102]C[-52]"
    Range("BS145:BS150").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-96]C[-51]"
'colone 1 pizza
'colone 2 pizza
    Range("BT150").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-100]C[-52]"
    Range("BU150").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-99]C[-53]"
    Range("BV150").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-98]C[-54]"
    Range("BW150").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-97]C[-55]"
    Range("BX150").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-96]C[-56]"
    Range("BY150").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-95]C[-57]"
    Range("BZ150").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-94]C[-58]"
    Range("CA150").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-93]C[-59]"
'colone 3 pizza
    Range("BT146:BT149").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-96]C[-51]"
    Range("BU146:BU149").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-95]C[-52]"
    Range("BV146:BV149").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-94]C[-53]"
    Range("BW146:BW149").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-93]C[-54]"
    Range("BX146:BX149").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-92]C[-55]"
    Range("BY146:BY149").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-91]C[-56]"
    Range("BZ146:BZ149").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-90]C[-57]"
    Range("CA146:CA149").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-89]C[-58]"
'colone 4 pizza
    Range("BT145").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-95]C[-47]"
    Range("BU145").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-94]C[-48]"
    Range("BV145").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-93]C[-49]"
    Range("BW145").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-92]C[-50]"
    Range("BX145").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-91]C[-51]"
    Range("BY145").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-90]C[-52]"
    Range("BZ145").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-89]C[-53]"
    Range("CA145").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-88]C[-54]"
'ligne prix pizza
    Range("CB151").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-93]C[-61]"
    Range("CB147:CB150").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-89]C[-60]"
        ' Range("CB145:CB146").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-87]C[-56]"
    
'ligne titre supp legume
    Range("BS143").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-94]C[-44]"
    Range("BS136:BS142").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-87]C[-43]"
'colone 1 supp legume
'colone 2 supp legume
    Range("BT142").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-92]C[-44]"
    Range("BU142").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-91]C[-45]"
    Range("BV142").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-90]C[-46]"
    Range("BW142").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-89]C[-47]"
    Range("BX142").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-88]C[-48]"
    Range("BY142").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-87]C[-49]"
    Range("BZ142").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-86]C[-50]"
    ' Range("CA142").Select
    ' ActiveCell.FormulaR1C1 = "=MENU!R[-85]C[-51]"
'colone 3 supp legume
    Range("BT137:BT141").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-87]C[-43]"
    Range("BU137:BU141").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-86]C[-44]"
    Range("BV137:BV141").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-85]C[-45]"
    Range("BW137:BW141").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-84]C[-46]"
    Range("BX137:BX141").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-83]C[-47]"
    Range("BY137:BY141").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-82]C[-48]"
    Range("BZ137:BZ141").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-81]C[-49]"
    ' Range("CA137:CA141").Select
    ' ActiveCell.FormulaR1C1 = "=MENU!R[-80]C[-50]"
'colone 4 supp legume
    Range("BT136").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-86]C[-38]"
    Range("BU136").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-85]C[-39]"
    Range("BV136").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-84]C[-40]"
    Range("BW136").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-83]C[-41]"
    Range("BX136").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-82]C[-42]"
    Range("BY136").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-81]C[-43]"
    Range("BZ136").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-80]C[-44]"
    ' Range("CA136").Select
    ' ActiveCell.FormulaR1C1 = "=MENU!R[-79]C[-45]"
'ligne prix supp legume
    Range("CB143").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-85]C[-53]"
    Range("CB138:CB142").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-80]C[-52]"
        ' Range("CB136:CB137").Select
        ' ActiveCell.FormulaR1C1 = "=MENU!R[-78]C[-47]"
    
'ligne info2
    Range("CC136:CC168").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-77]C[-79]"

'ligne prix commande
    Range("CD163:CD166").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-103]C[-78]"
    Range("CD160").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-100]C[-72]"
    Range("CD158:CD159").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-98]C[-71]"
    Range("CD155").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-95]C[-67]"
    Range("CD153:CD154").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-93]C[-66]"
    Range("CD150").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-90]C[-62]"
    Range("CD148:CD149").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-88]C[-61]"
    Range("CD145").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-85]C[-57]"
    Range("CD143:CD144").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-83]C[-56]"
    Range("CD140").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-80]C[-52]"
    Range("CD138:CD139").Select
    ActiveCell.FormulaR1C1 = "=MENU!R[-78]C[-51]"
    
    Sheets("TOT").Select
    Application.ScreenUpdating = True
    
End Sub

Le code est tres long, le screen update true/false ma deja fais gagné pas mal de temps lors du demarage de la macro.
Le but de cette macro est est de copier un menu format "paysage" et de le coller au format "portrait" sur 70 pages
Si quelqu'un a une idée a me proposer ou me mettre sur la voie en "allegeant" le debut de code pour que je puisse faire le reste.
Merciiiii

vbacrumble · 27 Février 2009

Re : Nettoyage de macro

Bonsoir

Déjà tu peux enlever les select

Voici comment
'ligne entete menu
Range("X162:X168").FormulaR1C1 = "=MENU!R[-160]C[-22]"
Range("X145:X156").FormulaR1C1 = "=MENU!R[-143]C[-10]"
Range("X139:X143").FormulaR1C1 = "=MENU!R[-137]C[3]"

Je te laisse continuer sur tout ton code.

Bon courage

patbeu · 27 Février 2009

Re : Nettoyage de macro

ok merci, je commence de suite ....🙂

soenda · 27 Février 2009

Re : Nettoyage de macro

Bonsoir Patbeu

Comme promis : ma question

' Je ne comprends pas ce que fait le code suivant, peux-tu me renseigner ?
' Même si ce n'est pas le plus grave

Sheets("E1").Select
ActiveWindow.ScrollWorkbookTabs Sheets:=46
Sheets(Array("E1", "E2", "E3", "E4", "E5", "E6", "E7", "E8", "E9", "E10", "E11", "E12", _
"E13", "E14", "E15", "E16", "E17")) _
.Select
Sheets("E1").Activate
Sheets(Array("E18", "E19", "E20", "E21", "E22", "E23", "E24", "E25", "E26", "E27", "E28", "E29", "E30", "E31", "E32", "E33", "E34", "E35", "E36", _
"E37", "E38", "E39", "E40", "E41", "E42", "E43", "E44", "E45", "E46", "E47", "E48", "E49", _
"E50")).Select Replace:=False
Sheets(Array("E51", "E52", "E53", "E54", "E55", "E56", "E57", "E58", "E59", "E60", "E61", _
"E62", "E63", "E64", "E65", "E66", "E67", "E68", "E69", "E70")).Select Replace:=False

Et pour le reste ***

' En écrivant les lignes suivantes
' --------------------------------
'ligne entete menu
Range("X162:X168").Select
ActiveCell.FormulaR1C1 = "=MENU!R[-160]C[-22]"
Range("X145:X156").Select
ActiveCell.FormulaR1C1 = "=MENU!R[-143]C[-10]"
Range("X139:X143").Select
ActiveCell.FormulaR1C1 = "=MENU!R[-137]C[3]"
'ligne adresse
Range("Z140:Z141").Select
ActiveCell.FormulaR1C1 = "=MENU!R[-136]C[3]"
Range("Z145:Z146").Select

' Sous la forme suivante
' ----------------------
Sheets("E1").Select
With Sheets("Menu")

' ligne entete
[X162:X168] = .[B2:B8]
[X145:X156] = .[N2:N13]
[X139:X143] = .[U2:U6]

' ligne adresse
[Z140:Z141] = .[X4:X5]
' ...

End With

' tu gagnera en lisibilité, mais surtout tu supprimeras
' quelques 600 ou 700 Select ! ! ! ...

A plus

soenda · 27 Février 2009

Re : Nettoyage de macro

Re, à tous

@patbeu : Penses à utiliser Edition/Remplacer

A plus

patbeu · 27 Février 2009

Re : Nettoyage de macro

soenda à dit:
' Je ne comprends pas ce que fait le code suivant, peux-tu me renseigner ?
' Même si ce n'est pas le plus grave

Sheets("E1").Select
ActiveWindow.ScrollWorkbookTabs Sheets:=46
Sheets(Array("E1", "E2", "E3", "E4", "E5", "E6", "E7", "E8", "E9", "E10", "E11", "E12", _
"E13", "E14", "E15", "E16", "E17")) _
.Select
Sheets("E1").Activate
Sheets(Array("E18", "E19", "E20", "E21", "E22", "E23", "E24", "E25", "E26", "E27", "E28", "E29", "E30", "E31", "E32", "E33", "E34", "E35", "E36", _
"E37", "E38", "E39", "E40", "E41", "E42", "E43", "E44", "E45", "E46", "E47", "E48", "E49", _
"E50")).Select Replace:=False
Sheets(Array("E51", "E52", "E53", "E54", "E55", "E56", "E57", "E58", "E59", "E60", "E61", _
"E62", "E63", "E64", "E65", "E66", "E67", "E68", "E69", "E70")).Select Replace:=False

c'est juste pour selectionner les feuilles de E1 à E70, je suis sur qu'il y a un autre moyen de le faire, mais c'est le code enregistré par VB
Merci pour les info, je met ca en pratique ce soir pour comparer la rapidité avec la suppression simple des .Select et ActiveCell

patbeu · 27 Février 2009

Re : Nettoyage de macro

Vbacrumbble merci, ca me fais deja gagné pas mal de ligne et surtout pas mal de temps (+/-3 seconde a la place de 10 minutes!!!!!) mais il y a un souci, au lieu de me coller les cell sur 70 page il ne me le fais plus que sur la premiere..... Je cherche pourquoi alors que je n'ai rien changer pour le reste. mais merci pour l'info

patbeu · 27 Février 2009

Re : Nettoyage de macro

vbacrumble à dit:
Bonsoir

Déjà tu peux enlever les select

Voici comment
'ligne entete menu
Range("X162:X168").FormulaR1C1 = "=MENU!R[-160]C[-22]"
Range("X145:X156").FormulaR1C1 = "=MENU!R[-143]C[-10]"
Range("X139:X143").FormulaR1C1 = "=MENU!R[-137]C[3]"

Je te laisse continuer sur tout ton code.

Bon courage

J'ai un truc bizarre... depuis que j'ai retiré les .select apres le Range, et le ActiveCell, au lieu de me copier les cellules sur les 70 pages, il ne le fait plus que sur la premiere (E1) alors que mon debuts de code n'as pas changer... As tu une idée de la raison Vbacrumble ??? Merci

soenda · 27 Février 2009

Re : Nettoyage de macro

Bonjour patbeu

Maintenant que tu as copié tes cellules sur la Feuilles "E2",

tu peux copier cette feuille sur les autres.

Tu auras bien supprimé tous ces "Select" 🙂

A plus

patbeu · 28 Février 2009

Re : Nettoyage de macro

ok en effet, beaucoup plus rapide qu'au debut Merci a tous

vbacrumble · 28 Février 2009

Re : Nettoyage de macro

Bonsoir

A titre de memo, cela peut toujours servir

Tu colles ton code VBA dans une feuille
Tu sélectionnes toutes lignes contenant désormais le code VBA.

Puis tu lances cette macro

Code:

Sub Macro1()
    Selection.Replace What:=").Select", Replacement:=").FormulaR1C1 _", LookAt _
        :=xlPart, SearchOrder:=xlByRows, MatchCase:=False
    Selection.Replace What:="ActiveCell.FormulaR1C1", Replacement:=" ", LookAt _
        :=xlPart, SearchOrder:=xlByRows, MatchCase:=False
    Selection.Copy
End Sub

En suite tu vas dans Microsoft Visual Basic Editor

Tu colles le contenu du presse-papier dans un module standard.

Ca soulagera tes doigts, ta souris et la touche efface 😉

patbeu · 28 Février 2009

Re : Nettoyage de macro

Merci pour l'info, mais j'avais copier le code dans word et fait la meme chose.... mais super pour les infos
Quand vbe me dis erreur code 9 ca viens d'ou ??
voici le code:

Code:

Sub SuppressionBonVide()

Dim i As Integer
Dim k As Integer
  Application.DisplayAlerts = False   
    For i = 1 To Worksheets.Count
      For k = 1 To 70
          If Sheets(i).Name = "" & k Then
              If Sheets(i).Range("C4") = "" Then Sheets(i).Delete
          End If
      Next k
    Next i
  Application.DisplayAlerts = True
End Sub

Merci

patbeu · 28 Février 2009

Re : Nettoyage de macro

En cherchant j'ai trouvé ca:
On Error Resume Next

ca marche, mais je ne sais pas si c'est la meilleur solution
Merci

Effectuez une recherche sur Excel Downloads...

Nettoyage de macro

patbeu

XLDnaute Nouveau

vbacrumble

XLDnaute Accro

patbeu

XLDnaute Nouveau

soenda

XLDnaute Accro

soenda

XLDnaute Accro

patbeu

XLDnaute Nouveau

patbeu

XLDnaute Nouveau

patbeu

XLDnaute Nouveau

soenda

XLDnaute Accro

patbeu

XLDnaute Nouveau

vbacrumble

XLDnaute Accro

patbeu

XLDnaute Nouveau

patbeu

XLDnaute Nouveau

Discussions similaires

Nous accordons de l'importance à votre vie privée