declaring variable cell mousecli

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Declaring variable of a cell by mouseclick & pasting

Declaring variable of a cell by mouseclick & pasting

resolvedResolved · High Priority · Version 2010

Dominique has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Excel VBA Advanced course

Declaring variable of a cell by mouseclick & pasting

Hi,

I am trying to make a macro which copies data from one spreadsheet into another, using a message box to hold the cell that the paste starts from, and would like to define this by mouseclick rather than the user typing in the cell reference. After adding the mouseclick script, the paste is not working. I have as follows:

Sub DQSIReformatCopyAndPasteV2()
'
' DQSIReformatCopyAndPasteV2 Macro
'
Dim StartCell As Range

'This bit is just to reformat the data I want to copy into the right layout

Windows("bF_dq_cids_ServiceImprovement_fields(1).xls").Activate
Rows("9:9").Select
Selection.Delete Shift:=xlUp
Rows("15:15").Select
Selection.Delete Shift:=xlUp
Rows("16:16").Select
Selection.Delete Shift:=xlUp
Range("I16:P16").Select
Selection.ClearContents
Columns("J:J").Select
Selection.Delete Shift:=xlToLeft
Range("I12:O17").Select
Selection.Copy

'This bit is going into the sheet I want to copy into

Windows("AD template DQ example 2015.12.17.xlsm").Activate

'This is the bit I think should be where you get a message to select the cell you wish to paste from:

Set StartCell = Application.InputBox(Prompt:= _
"Please select the cell you wish to start pasting into.", _

'This bit is also part of the mouseclick script - I don't know what this means
Title:="SPECIFY RANGE", Type:=8)

'This should be selecting the cell that the paste should start from

StartCell.Select

'This is where it all goes horribly wrong:

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("E7").Select
End Sub


If anyone is able to help I'd greatly appreciate it.

Thanks!

Dom

RE: Declaring variable of a cell by mouseclick & pasting

Hi Dom

It is possible to write a macro allowing you to click where to paste via an input box. However, my colleague suggested using an array variable rather than copy and paste.

Here is the code for a procedure that copies the a selected range into an array and prompts for where to paste the array data.

Sub ArrayCopyAndPaste()
Dim InpData As Variant
InpData = Selection

Dim Rng As Range

Set Rng = Application.InputBox(Prompt:= _
"Please select a range with your Mouse", _
Title:="SPECIFY RANGE", Type:=8)
' Type:=8 allows the Inputbox to accept a range

'the next line resizes the output range (Rng) to the dimensions of InpData (1 stands for row and 2 for column)

Rng.Resize(UBound(InpData, 1), UBound(InpData, 2)) = InpData

End Sub

In your macro create an array variable (InpData) and a range variable (Rng)

Dim InpData As Variant
Dim Rng As Range

Then replace Selection.Copy with
InpData= selection


Replace the line Set StartCell = with

Set Rng = Application.InputBox(Prompt:= _
"Please select a range with your Mouse", _
Title:="SPECIFY RANGE", Type:=8)

Rng.Resize(UBound(InpData, 1), UBound(InpData, 2)) = InpData



My colleague is not available but please ask if you want more clarification regarding arrays.

Regards
Doug
Best STL


Tue 29 Dec 2015: Automatically marked as resolved.

 

Training courses

 

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Closing Multiple Workbooks quickly

When you have several workbooks open in Excel and want to just close them all at once:

1) Hold down the SHIFT key before selecting the File menu.

2) Once in File menu release SHIFT key and select Close All option.

3) All your files will close. If files require saving Excel will ask if you want to save the changes.

View all Excel hints and tips


Server loaded in 0.06 secs.