Error : That command cannot be used on multiple selections

Question

Good Day,

Can anyone advice me on how to select multiple selections and copy the selections and paste into the database.

Highlighted in red is the selection which i want to copy and paste in the database. Thanks

Here is the error message:

Answer 1

Up to now I have only been looking at specific code that you have said is a problem but today I have looked a bit further into your project and I realized that the next and previous record arrows were not returning the correct values into the form so I re-coded that part also.

Again, I have kept the coding simple and the Next and Previous record now updates the cell at L5 (the dropdown validation) and that causes it to automatically call the worksheet change event that populates the form from the database so it is using the same code to populate the form as when a record is selected from the Data Validation DropDown.

Latest version at the following link.

****Link Removed****

Answer 2

do i need to create another module and paste your VBA code or should i just paste in same module.

The code I posted is just an example of how you might be able to code for your requirements because multiple areas cannot be copied and pasted as one operation. From the code that you posted, I assumed that you are fairly competent with VBA and would be able to edit the code for your requirements. However, with your latest screen capture of the destination, I am now assuming that you want all of the source data to be pasted into one row in the destination. If my assumption is correct then copy each cell separately and paste to the appropriate cell in the destination.

If you need further help then I will need a copy of your workbook. Guidelines to upload a workbook on OneDrive. (If you already use OneDrive and your process for saving to it is different then you can probably start at step 8 to get the link but please zip the file before uploading.)

1. Zip your workbooks. Do not just save an unzipped workbook to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
2. To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder). By holding the Ctrl key and left click once on each file, you can select multiple workbooks before right clicking over one of the selections to send to a compressed file and they will all be included into the one Zip file.
3. Do not use 3rd party compression applications because I cannot unzip them. I do not clog up my computer with 3rd party apps when there are perfectly good apps supplied with windows.
4. Go to this link.  https://onedrive.live.com
5. Use the same login Id and Password that you use for this forum.
6. Select Upload in the blue bar across the top and browse to the zipped folder to be uploaded.
7. Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
8. Right click the file on OneDrive and select Share.
9. Select "Get a Link" from the popup menu.
10. Click Copy the link and Paste into your reply on this forum.

Answer 3

Note that I have edited the code in my previous post to include the line to Copy the ranges.

This post just to advise you of the edit because you do not get an email notification when posts are edited.

sorry OssieMac, do i need to create another module and paste your VBA code or should i just paste in same module.

kindly advice me on this.thanks

here is my destination in new sheet and all those selected range should paste from C2 to end.

Answer 4

Note that I have edited the code in my previous post to include the line to Copy the ranges.

This post just to advise you of the edit because you do not get an email notification when posts are edited.

Answer 5

With the information supplied I can only provide you with some guidelines.

Each area of a non contiguous range must be pasted separately. I don't know what the shape of the destination is so I don't know if each area also needs to be pasted as separate rows.

The separate ranges that make up a range of non contiguous cells are referenced as Areas in VBA code.

'Following example will separate the copy range into separate areas for pasting

Sub TestExample_1()

    Dim myCopy As Range

    Dim rngArea As Range

    Set myCopy = Selection

    For Each rngArea In myCopy.Areas

        rngArea.Copy

        'Destination address here and Paste special rngArea

        MsgBox rngArea.Address      'Used for testing only

    Next rngArea

End Sub

'Following will separate the copy range into separate areas

'and then separate each of the areas into separate rows for pasting

'Note that the code only references the row range of the area (NOT the entire row on worksheet)

Sub TestExample_2()

    Dim myCopy As Range

    Dim rngArea As Range

    Dim rngRow As Range

    Set myCopy = Selection

    'mycopy

    For Each rngArea In myCopy.Areas

        'Separate each row of the area

        For Each rngRow In rngArea.Rows

            rngRow.Copy

            'Destination cell address here and PasteSpecial rngRow

            MsgBox rngRow.Address   'Used for testing only

        Next rngRow

    Next rngArea

End Sub