Non éxecution d'une macro au retour au classeur d'origine

Le 13/12/2011 à 17:37

Bonjour le forum.

J'ai écris un code qui copie les valeurs d'une feuille et la colle vers une autre et ca marche : le voici

Private Sub CommandButton1_Click()
mois = InputBox("mm/aaaa", "Choix du mois à mettre à jour")
Range("O1").Select
ActiveCell = mois
Set YTDNCSM = ThisWorkbook.Sheets("YTD NCSM")
Set MNCSM = ThisWorkbook.Sheets("M NCSM")
Set YTDNCS = ThisWorkbook.Sheets("YTD NCS")
Set MNCS = ThisWorkbook.Sheets("M NCS")
Set YTDN = ThisWorkbook.Sheets("YTD Nice")
Set MN = ThisWorkbook.Sheets("M Nice")
Set YTDC = ThisWorkbook.Sheets("YTD Cannes")
Set MC = ThisWorkbook.Sheets("M Cannes")
Set YTDS = ThisWorkbook.Sheets("YTD Sophia")
Set MS = ThisWorkbook.Sheets("M Sophia")
Set YTDM = ThisWorkbook.Sheets("YTD Monaco")
Set MM = ThisWorkbook.Sheets("M Monaco")
filename1 = Application.GetOpenFilename("Excel Files(*.xls), *.*", , "Selectionnez votre fichier export ")
If Not filename1 = "" Then
End If
Set wB = Workbooks.Open(Filename:=filename1)

wB.Sheets("NCSM cumul").Range("G24:H25").Copy
YTDNCSM.Range("B4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  Application.CutCopyMode = False
wB.Sheets("NCSM cumul").Range("G27:H28").Copy
YTDNCSM.Range("B6").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  Application.CutCopyMode = False
wB.Sheets("NCSM cumul").Range("G31:H33").Copy
YTDNCSM.Range("B8").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  Application.CutCopyMode = False
  wB.Sheets("NCSM cumul").Range("K24:L25").Copy
YTDNCSM.Range("B31").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  Application.CutCopyMode = False
  wB.Sheets("NCSM cumul").Range("K27:K28").Copy
YTDNCSM.Range("B33").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  Application.CutCopyMode = False
  wB.Sheets("NCSM cumul").Range("K31:K33").Copy
YTDNCSM.Range("B35").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  Application.CutCopyMode = False

Je voudrais répéter ce procédé, j'ai donc écrit

Private Sub CommandButton1_Click()
mois = InputBox("mm/aaaa", "Choix du mois à mettre à jour")
Range("O1").Select
ActiveCell = mois
Set YTDNCSM = ThisWorkbook.Sheets("YTD NCSM")
Set MNCSM = ThisWorkbook.Sheets("M NCSM")
Set YTDNCS = ThisWorkbook.Sheets("YTD NCS")
Set MNCS = ThisWorkbook.Sheets("M NCS")
Set YTDN = ThisWorkbook.Sheets("YTD Nice")
Set MN = ThisWorkbook.Sheets("M Nice")
Set YTDC = ThisWorkbook.Sheets("YTD Cannes")
Set MC = ThisWorkbook.Sheets("M Cannes")
Set YTDS = ThisWorkbook.Sheets("YTD Sophia")
Set MS = ThisWorkbook.Sheets("M Sophia")
Set YTDM = ThisWorkbook.Sheets("YTD Monaco")
Set MM = ThisWorkbook.Sheets("M Monaco")
filename1 = Application.GetOpenFilename("Excel Files(*.xls), *.*", , "Selectionnez votre fichier export ")
If Not filename1 = "" Then
End If
Set wB = Workbooks.Open(Filename:=filename1)

wB.Sheets("NCSM cumul").Range("G24:H25").Copy
YTDNCSM.Range("B4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  Application.CutCopyMode = False
wB.Sheets("NCSM cumul").Range("G27:H28").Copy
YTDNCSM.Range("B6").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  Application.CutCopyMode = False
wB.Sheets("NCSM cumul").Range("G31:H33").Copy
YTDNCSM.Range("B8").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  Application.CutCopyMode = False
  wB.Sheets("NCSM cumul").Range("K24:L25").Copy
YTDNCSM.Range("B31").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  Application.CutCopyMode = False
  wB.Sheets("NCSM cumul").Range("K27:K28").Copy
YTDNCSM.Range("B33").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  Application.CutCopyMode = False
  wB.Sheets("NCSM cumul").Range("K31:K33").Copy
YTDNCSM.Range("B35").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  Application.CutCopyMode = False

wB.Sheets("NCSM M").Range("G24:H25").Copy
MNCSM.Range("B4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  Application.CutCopyMode = False
wB.Sheets("NCSM M").Range("G27:H28").Copy
MNCSM.Range("B6").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  Application.CutCopyMode = False
wB.Sheets("NCSM M").Range("G31:H33").Copy
MNCSM.Range("B8").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  Application.CutCopyMode = False
  wB.Sheets("NCSM M").Range("K24:L25").Copy
MNCSM.Range("B31").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  Application.CutCopyMode = False
  wB.Sheets("NCSM M").Range("K27:K28").Copy
MNCSM.Range("B33").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  Application.CutCopyMode = False
  wB.Sheets("NCSM M").Range("K31:K33").Copy
MNCSM.Range("B35").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  Application.CutCopyMode = False

Mais là j'ai une erreur d'execution à la première ligne de la dernière série d'instruction (wB.Sheets("NCSM M").Range("G24:H25").Copy). Quelqu'un saurait-il pourquoi?

Merci d'avance

Le 13/12/2011 à 18:12

Bonsoir

Le message d'erreur pourrait indiquer la source du problème

Rechercher des sujets similaires à "execution macro retour classeur origine"