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
