Macro copie des cellules a la derniere case

[Résolu/Fermé]
Signaler
-
 mister-t -
Bonjour,

voila j'ai un probleme avec ma macro. Je copie des cellules sur un autre classeur et je les copie et colle en dernière position dans ce classeur, a la suite de la liste deja presente.

Toutefois lorsque j'utilise ma macro, le debogueur ne me dit rien et pourtant les cellules ne se collent pas a la suite de la liste mais quelques lignes en dessous Pourquoi ?

De plus si je reintere l'operation une nouvelle fois, dans ce cas la il execute la macro mais ne colle rien pourquoi?

please Help me

la journée devant un pc ça fais mal aux yeux

macro:

Sub macro2()

Dim wbk1 As Workbook
Dim wbk2 As Workbook
Dim h As String
Dim x, y, z As Integer


Set wbk1 = ThisWorkbook

h = UserForm1.TextBox2.Text
x = Cells(6, 11).Value
y = 4
z = x + y

Set wbk2 = Workbooks.Open(FileName:="dimensi.1998 a 2008")

ColonneA = "A"
With Sheets("Non conforme")
DerLig = .Range(ColonneA & "65536").End(xlUp).Row + 1
Range(ColonneA & DerLig) = wbk1.Sheets("PV").Cells(6, 11)
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Font.Bold = False
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.name = "Times New Roman"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With

ColonneB = "B"
With Sheets("Non conforme")
DerLig = .Range(ColonneB & "65536").End(xlUp).Row + 1
Range(ColonneB & DerLig) = wbk1.Sheets("PV").Cells(16, 5)
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Font.Bold = False
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.name = "Times New Roman"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With


ColonneC = "C"
With Sheets("Non conforme")
DerLig = .Range(ColonneC & "65536").End(xlUp).Row + 1
Range(ColonneC & DerLig) = wbk1.Sheets("PV").Cells(9, 3)
End With

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Font.Bold = False
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.name = "Times New Roman"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
Selection.Font.ColorIndex = 3


ColonneD = "D"
With Sheets("Non conforme")
DerLig = .Range(ColonneD & "65536").End(xlUp).Row + 1
Range(ColonneD & DerLig) = wbk1.Sheets("PV").Cells(16, 15)
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Font.Bold = False
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.name = "Times New Roman"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With


ColonneE = "E"
With Sheets("Non conforme")
DerLig = .Range(ColonneE & "65536").End(xlUp).Row + 1
Range(ColonneE & DerLig) = wbk1.Sheets("PV").Cells(34, 8)
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Font.Bold = False
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.name = "Times New Roman"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With



ColonneF = "F"
With Sheets("Non conforme")
DerLig = .Range(ColonneF & "65536").End(xlUp).Row + 1
Range(ColonneF & DerLig) = wbk1.Sheets("PV").Cells(14, 3)
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Font.Bold = False
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.name = "Times New Roman"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With



ColonneG = "G"
With Sheets("Non conforme")
DerLig = .Range(ColonneG & "65536").End(xlUp).Row + 1
Range(ColonneG & DerLig) = wbk1.Sheets("PV").Cells(14, 10)
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Font.Bold = False
With Selection
.WrapText = False
.Orientation = 0
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.name = "Times New Roman"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
Selection.Font.ColorIndex = 3

Application.DisplayAlerts = False
wbk2.Save
wbk2.Close

Application.DisplayAlerts = True

Dim copyname As String
copyname = Range("numeroPV")
wbk1.SaveCopyAs FileName:="C:\Documents and Settings\MARTINR\Mes documents\PV dimension " & h & "\" & copyname

End Sub

1 réponse

Messages postés
17095
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
15 octobre 2021
906
A tout hasard, je commencerais par changer ceci :

ColonneA = "A"
With wkb?.Sheets("Non conforme") ' mettre le workbook où se trouve la sheet
DerLig = .Range(ColonneA & "65536").End(xlUp).Row + 1
.Range(ColonneA & DerLig) = wbk1.Sheets("PV").Cells(6, 11) 'mettre un point en début de ligne

Il vaut mieux éviter de laisser Excel décider...
merci pour cette reponse

si j'applique ce que tu as ecri sur une macro qui a pour destination

with wbk2.sheets("Probleme")

alors les copier coller s'appliquent aux colonnes que j'ai défini et a la suite de la liste

Toutefois si je veux faire la meme chose sur une autre macro et que je definis la destination du type

with wbk2.sheets("Non conforme")

alors il y a un probleme.Pas au niveau du debugueur mais au niveau des copier coller.Seulement la cellule située sur la colonne D se voit attribuer le copier coller. Cela se fait a la suite de la liste mais que la cellule de la colonne D.

les autres copier coller sur les cellules des colonnes a b c e f.... ne marchent pas

Si tu as une reponse a ce probleme elle est bienvenue
Messages postés
17095
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
15 octobre 2021
906 > mister-t
Pour les autres colonnes :
- es tu certain que les cellules ne sont pas collées beaucoup plus bas ?
- tu peux montrer le code modifié ?
>
Messages postés
17095
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
15 octobre 2021

salut yg_be

le probleme est reglé en fait il devait y avoir un bug sur la feuille parce que je l'ai supprimé et j'en ai recréé une portant le meme nom et ça a marché. Les cellules se collent ou il faut

merci a toi pour ces réponses eclairées

probleme resolu