CODEWITHSUNDEEP
vbaexcel
Nico Coallier
Nico Coallier

Reputation: 686

Not copying the values properly

I have a problem with my macro to copy paste only the values of the range A6:AM46,A52:AM84 to AN6 location on the same sheet.

 Sub PréparerGrilles()


    Range("A6:AM46,A52:AM84").Select
    Selection.Copy

    Range("AN6").Select
    Application.CutCopyMode = False
    ActiveSheet.Paste

End Sub

I get the 1004 error (multiple selection error) Could you help me with this ?

Upvotes: 0

Views: 53

Answers (2)

Damian
Damian

Reputation: 5174

Application.CutCopyMode = False cleans the clipboard....

Better code for that is:

 Sub Test()

    Dim wb As Workbook, ws As Worksheet

    Set wb = ThisWorkbook
    Set ws = wb.Sheets("SOMMAIRE") 'this means it will only work on this sheet, you should change the name of the sheet or ask me what do you want in order to get it working on other sheets

    ws.Range("A6:AL46").Copy
    ws.Range("AN6").PasteSpecial xlValues

    ws.Range("A52:AM84").Copy
    ws.Range("AN52").PasteSpecial xlValues

 End Sub

Edited: Now that should do the trick. Try it out and tell me if it works

Edited2: This is what you want, at least for what you asked so far.

Upvotes: 1

YowE3K
YowE3K

Reputation: 23974

To copy values from A6:AM46,A52:AM84 to AN6:BZ46,AN52:BZ84 you can do the following:

Sub PreparerGrilles()
    Range("AN6:BZ46").Value = Range("A6:AM46").Value
    Range("AN52:BZ84").Value = Range("A52:AM84").Value
End Sub

Version using the Range.Copy method:

Sub PreparerGrilles()
    Range("A6:AM46").Copy Destination:=Range("AN6:BZ46")
    Range("A52:AM84").Copy Destination:=Range("AN52:BZ84")
    Range("AN6:BZ46").Value = Range("AN6:BZ46").Value
    Range("AN52:BZ84").Value = Range("AN52:BZ84").Value
End Sub

I recommend that you don't slow your code down by using this. It will also lead to potentially incorrect values if your formulae refer to anything that wasn't part of the copy.

Version using a PasteSpecial xlPasteValues method:

Sub PreparerGrilles()
    Range("A6:AM46").Copy
    Range("AN6:BZ46").PasteSpecial xlPasteValues
    Range("A52:AM84").Copy
    Range("AN52:BZ84").PasteSpecial xlPasteValues
End Sub

I strongly recommend against using this method, as it leads to too many "unreproducible" errors due to the users copying things via the clipboard between when your code does the Copy and when it does the Paste, and also because of the fact that your Copy zaps whatever the user might have manually pasted to the clipboard.

Upvotes: 1

Related Questions