multiple selection - vba

[lezawang]

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.

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

 

[alansidman]

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:

Sub myselection()
Dim dest as string
dest = Inputbox("Where to paste?,ie A4")
Selection.copy Range(dest)
End Sub

 

[lezawang]

thanks for your idea. I will do that. the purpose of what I wanted to do is to learn vba basically. thanks once again

 

[My Aswer Is This]

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:

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

 

[lezawang]

Thank you so much for this code. I tried it and it is amazing. I commented .

   'Application.ScreenUpdating = False
    'Application.ScreenUpdating = true

What these 2 lines of code do? Thanks once again

 

[My Aswer Is This]

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.

 

[alansidman]

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.

Application.Cutcopymode = False

Just some good code practices.

 

[MARK858]

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.

 

[alansidman]

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

 

[MARK858]

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

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