Determine current selections source worksheet

[Thebatfink]

Hi,

As the topic, I have a macro which is manipulating data which has been copied and then triggered using a right click menu item which fires the macro (rather than using the standard right click paste)..

I have a requirement in my macro to check which worksheet the currently copied selection is from prior to performing my manipulation. Is it possible to get the worksheet codename of the currently copied selection in vba?? or am I dreaming..

Thanks!
Batfink

 

[TiaXL]

if you mean what I think you mean then make a variable like

dim sWSname as string

and then
sWSname = activesheet.name

 

[Thebatfink]

My problem with that is that the macro is basically an alternative right click paste option.. So the flow of this is ..

- User copies range selection on Sheet1 or Sheet2 using right click menu

- User right clicks on Cell in Sheet3 and selects alternative paste option

the process of triggering the macro is making the activesheet Sheet3 - the destination sheet, not the source sheet.

I should of said the pasting is always on a different sheet to that of the selections source sheet.

Cheers
Batfink

 

[RoryA]

I don't believe you can access that information directly. You might be able to paste link first, parse the linking formula to get the sheet info, then do your actual paste, but that first bit might also clear the clipboard (haven't tested).

 

[Thebatfink]

What do you mean by Paste Link?

I'm wondering if a solution might be to possibly have an alternate copy macro, as well as an alternate paste macro and grab the activesheet then? But I'm not sure how to store that variable so the macro in my workbook module for pasting can access it.

Is it possible to capture every standard application "copy" action somehow and avoid a second copy option in the right click menu?

Thanks

 

[RoryA]

You can do a Paste Link which basically just creates formulas linking one range to another.

It would be easier to either have your own Copy routine, or have your Paste routine prompt for cells to copy before it does anything else.

As a matter of interest, why does it matter?

 

[Thebatfink]

Ahh I never thought about an application.inputbox I will see how that works out, nice and simple Shame about capturing copies though

Part of the manipulation of the data is adding a number to certain cells and that number is different if the data is from sheet1 to that of sheet2, but finished manipulated data from both those sheets is always consolidated on sheet3.. hence why I needed to know the source.

Thanks for the ideas guys Appreciated!

Batfink

 

[Thebatfink]

Ahhh, I'm back to my original question :/ Ok, if I use this code to prompt a copy, how can I determine the worksheet of the selected range.

Dim copyrange As Range

On Error Resume Next

Application.DisplayAlerts = False

    Set copyrange = Application.InputBox(Prompt:= _
    "Select Range", Type:=8)

    On Error GoTo 0

Application.DisplayAlerts = True
        
copyrange.Copy
        
Sheet3.Range("A1").PasteSpecial (xlPasteValues)
Application.CutCopyMode = False

Thanks

 

[RoryA]

copyrange.worksheet

is a Worksheet object.

 

[Thebatfink]

If I do something like

copysheet = copyrange.worksheet

it errors out though? Object doesn't support this method or property.