multiple selection - vba

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,805
Office Version
  1. 2016
Platform
  1. Windows
Hi
I want to copy the selected cells to another part of the sheet by selecting the destination range as well. right now I can only use the destination as a cell/range as it is shown in the code below. I wonder if the destination range can be selected as well (by using the mouse). I tried to select both using CTRL key but then excel gave "multiple selection" error message. Than you.

Code:
Sub myselction()
[INDENT]Selection.Copy Destination:=Cells(10, 10)[/INDENT]
End Sub
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
If want to use the mouse to indicate where to paste, then what would be the purpose of creating a macro.

If you wish to have some control over the destination as it may vary, then I suggest you use an Input Box to give the user the option of selecting.

Something like this:

Code:
Sub myselection()
Dim dest as string
dest = Inputbox("Where to paste?,ie A4")
Selection.copy Range(dest)
End Sub
 
Upvote 0
thanks for your idea. I will do that. the purpose of what I wanted to do is to learn vba basically. thanks once again
 
Upvote 0
If there was any trend to your choice you could use something like this:
Try it and see what it does.

Like if you always want to copy the selected cells to a cell five cells to the right and one cell down

You could try this:

Code:
Sub Selection_Copy()
'Modified  10/19/2018  11:35:58 PM  EDT
Application.ScreenUpdating = False
Selection.Copy Selection.Offset(1, 5)
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Thank you so much for this code. I tried it and it is amazing. I commented .
Rich (BB code):
   'Application.ScreenUpdating = False
    'Application.ScreenUpdating = true


What these 2 lines of code do? Thanks once again
 
Upvote 0
The above two lines of code keep the screen from updating when the code runs.

If you have a long script doing a lot of things this may speed up the script and stop some screen flickering during the script.

It's a few lines of code a lot of people add to every script.

With just the script I provided I really don't think it's needed.
But if I do not included it someone might jump in and say you should add it.

Sort of like:
Do those bugs on your windshield slow down your car?
Well sure they do. They cause drag. But how much drag?

But on long scripts where your copying and pasting and deleting rows and all sorts of stuff I'm sure turning off screen updating can help. And some people time their scripts down to the millisecond.
 
Upvote 0
In addition to what "My Answer Is This" has stated, I usually add a line toward the end of my code if there has been copying and pasting that kind of clears of being highlighted the last range copied.

Code:
Application.Cutcopymode = False

Just some good code practices.
 
Upvote 0
You don't get the marching ants you get using Copy/Paste when using Copy/Destination which My Aswer Is This uses in post number 4.
 
Last edited:
Upvote 0
@MARK858
True, but you do when you use PasteSpecial which is often the case if you want to preserve values or you are doing a transpose. These are often requests in this and other forums. And I do it as a general practice, just because.
 
Last edited:
Upvote 0
Hi Alan, my personal preference is to only put in the code necessary so I wouldn't use Application.Cutcopymode = False when using Destination although I would always when using Paste/PasteSpecial.

Having said that there is no real need to use PasteSpecial to preserve values as you can just use something like the below based on the code from My Aswer Is This (not much you can do about the transpose without using arrays though).

Code:
Sub Selection_Values()
    Application.ScreenUpdating = False
    With Selection
        .Offset(1, 5).Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With
    Application.ScreenUpdating = True
End Sub

Although even the Application.ScreenUpdating = False is redundant in this case as it is only a single action being taken.

Everything is personal preference though when writing VBA :ROFLMAO:
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top