Macro copie des cellules a la derniere case

Résolu

mister-t -
mister-t - 13 juin 2008 à 13:14

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

Afficher la suite

A voir également:

Macro copie des cellules a la derniere case
Dernière version ccleaner gratuit français - Télécharger - Nettoyage
Copie cachée - Guide
Telecharger macro convertir chiffre en lettre excel - Télécharger - Tableur
Supprimer dernière page word - Guide
Super copie - Télécharger - Gestion de fichiers

1 réponse

Réponse 1 / 1

yg_be Messages postés 23541 Date d'inscription Statut Contributeur Dernière intervention Ambassadeur 1 584

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...

mister-t

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

yg_be Messages postés 23541 Date d'inscription Statut Contributeur Dernière intervention 1 584 > 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é ?

mister-t > yg_be Messages postés 23541 Date d'inscription Statut Contributeur Dernière intervention

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

Discussions similaires

Copier un lien instagram

Retrouver liens copiés sur le téléphone

copie d'un lien dans le navigateur internet

Votre réponse

Discussions similaires