Erreur macro la méthode rang de l "objet" global a échouée
Bonjour à tous
J ai un blocage sur une macro, j ai mis au bout d une autre un ajout celle ci bloque avec une erreur
la méthode rang de l objet global a échouée
si je peux avoir un coup de main la dessus
Sub shop21()
' shop21 Macro
Rows("1:2").Select
Selection.Delete Shift:=xlUp
Rows("1:1").Select
Selection.ClearContents
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
Columns("E:E").EntireColumn.AutoFit
Columns("F:F").EntireColumn.AutoFit
Columns("G:G").Select
ActiveWindow.SmallScroll Down:=-3
Selection.Delete Shift:=xlToLeft
Columns("G:G").EntireColumn.AutoFit
Columns("H:H").Select
Selection.Delete Shift:=xlToLeft
Columns("I:O").Select
Selection.Delete Shift:=xlToLeft
Range("A15").Select
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = ""
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With
Application.PrintCommunication = True
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = ""
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With
Application.PrintCommunication = True
ActiveWindow.SmallScroll Down:=33
Range("A1:H59").Select
Range("H59").Activate
ActiveSheet.PageSetup.PrintArea = "$A$1:$H$59"
ActiveSheet.PageSetup.PrintArea = "$A$1:$H$59"
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = "$A$1:$H$59"
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = "&D&T"
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.236220472440945)
.RightMargin = Application.InchesToPoints(0.236220472440945)
.TopMargin = Application.InchesToPoints(0.748031496062992)
.BottomMargin = Application.InchesToPoints(0.748031496062992)
.HeaderMargin = Application.InchesToPoints(0.31496062992126)
.FooterMargin = Application.InchesToPoints(0.31496062992126)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintSheetEnd
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 2
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
Range("Tableau_DonnéesExternes_118[[#Headers],[Colonne1]]").Select j ai ajoute a partir de cette ligne un morceau d une autre macro
ActiveCell.FormulaR1C1 = "ATELIER"
Range("Tableau_DonnéesExternes_118[[#Headers],[Colonne2]]").Select
ActiveCell.FormulaR1C1 = "MODEL"
Range("Tableau_DonnéesExternes_118[[#Headers],[Colonne3]]").Select
ActiveCell.FormulaR1C1 = "UNIT"
Range("Tableau_DonnéesExternes_118[[#Headers],[Colonne4]]").Select
ActiveCell.FormulaR1C1 = "LICENSE"
Range("Tableau_DonnéesExternes_118[[#Headers],[Colonne5]]").Select
ActiveCell.FormulaR1C1 = "CUSTOMER"
Range("Tableau_DonnéesExternes_118[[#Headers],[Colonne6]]").Select
ActiveCell.FormulaR1C1 = "KMS"
Range("Tableau_DonnéesExternes_118[[#Headers],[Colonne7]]").Select
ActiveCell.FormulaR1C1 = "NOREV"
Range("Tableau_DonnéesExternes_118[[#Headers],[Colonne8]]").Select
ActiveCell.FormulaR1C1 = "REMARK"
Range("H2").Select
End With
End Sub
Edit modo : code mis entre balises
Bonjour Patogaze77,
Non la, vous avez intégré une image, mais pour présenter ou se situe le blocage, c'est bien
Si nous pouvions avoir un fichier anonymisé ce serait encore mieux...
nous pourrions faire des tests, vérifier l'erreur et certainement la corriger
Pour insérer un fichier, c'est le bouton
Pour anonymiser le fichier, c'est ici
https://www.excel-pratique.com/fr/utilitaires/anonymisation-donnees
A+
Merci Bruno
Re,
Bon ce n'est pas ça qui est ça, tout est anonymisé, plus de tableau structuré et plus de macro
Ceci dit en regardant votre code, voici ce que j'ai fait
Sub shop21()
' Avec la feuille active
With ActiveSheet
.Rows("1:2").Delete Shift:=xlUp
.Rows("1:1").ClearContents
.Columns("B:B").Delete Shift:=xlToLeft
.Columns("A:D").EntireColumn.AutoFit
.Columns("E:E").Delete Shift:=xlToLeft
.Columns("E:F").EntireColumn.AutoFit
.Columns("G:G").Delete Shift:=xlToLeft
.Columns("G:G").EntireColumn.AutoFit
.Columns("H:H").Delete Shift:=xlToLeft
.Columns("I:O").Delete Shift:=xlToLeft
'
.PageSetup.PrintArea = "$A$1:$H$59"
Application.PrintCommunication = False
' Avec la configuration de l'impression
With .PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
.LeftHeader = "&D&T"
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.236220472440945)
.RightMargin = Application.InchesToPoints(0.236220472440945)
.TopMargin = Application.InchesToPoints(0.748031496062992)
.BottomMargin = Application.InchesToPoints(0.748031496062992)
.HeaderMargin = Application.InchesToPoints(0.31496062992126)
.FooterMargin = Application.InchesToPoints(0.31496062992126)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintSheetEnd
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 2
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
End With
' de retour sur la feuille
.Range("Tableau_DonnéesExternes_118[[#Headers],[Colonne1]]").Value = "ATELIER"
.Range("Tableau_DonnéesExternes_118[[#Headers],[Colonne2]]").Value = "MODEL"
.Range("Tableau_DonnéesExternes_118[[#Headers],[Colonne3]]").Value = "UNIT"
.Range("Tableau_DonnéesExternes_118[[#Headers],[Colonne4]]").Value = "LICENSE"
.Range("Tableau_DonnéesExternes_118[[#Headers],[Colonne5]]").Value = "CUSTOMER"
.Range("Tableau_DonnéesExternes_118[[#Headers],[Colonne6]]").Value = "KMS"
.Range("Tableau_DonnéesExternes_118[[#Headers],[Colonne7]]").Value = "NOREV"
.Range("Tableau_DonnéesExternes_118[[#Headers],[Colonne8]]").Value = "REMARK"
End With
End Sub
A tester
oui Bruno merci je me doutais un peu, c'est pas bon, je vais refaire un beau fichier anonymisé avec la macro a l intérieur
merci
Bonjour Patogaze , Bonjour Bruno j'étais encore en train de chercher le pourquoi de tous ces select et de ces formula
je vais chercher un autre solution merci les gars