Paste upon click

  • Thread starter Thread starter bb pcv
  • Start date Start date
B

bb pcv

I would like to write a macro, to be attached to a label, that upon clicking
the label, would put the named range on the clipboard, and then paste the
range at a cell in the worksheet with another click.
current code:
Sub More_time()
Range("More_Time").Select
Selection.Copy
End Sub
would like to paste by clicking
 
This assumes the label is from the Control Toolbox and is on the same sheet
as the named cell, and that the receiving cell is on the same page. Copy
both subs to the sheet code module. Click the label, then click a cell.
Don't forget to save the file to keep the code.
You can name the label and update the code, in case you have more than one
label.

Private Sub Label1_Click()
Range("More_Time").Copy
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.PasteSpecial xlPasteValues
End Sub
 
I could not get this code to work.
Maybe a fuller explaination of what I'm trying to do will help.
I have 10 employees who see clients throughout the day. Appointment lengths
are predicated on the number of programs clients are requesting. The
worksheet has the employees in Column A and the times in Columns B:AS. Each
cell in the appointment grid represents 15 minutes. At the top of the grid,
I've named ranges (a, b, c, etc) that represent different lengths of
appointments, and color coded the ranges so that the different appointment
types are readily distinguishable when pasted into the main grid. I have
placed a Forms Label over each of the appointment types and attached a macro
that slects the range and places it on the clipboard (Range("A").select
Selection.Copy)
The user (Receptionist) clicks on the appropriate appointment type -
launching the macro - and then places the cursor in the intersection of time
and employee. To paste the selection, they must use the keyboard shortcut or
right click.
I'd like to have them select the range with one click and then paste with
another.

Thanks for any help you can give...
 
As I read your description of the current set up, it seems that all you need
is the code to paste the clipboard contents on the sheet. The code below
will do thatl Right click the sheet tab and click view code on the pop up
menu. Then paste this snippet into the code window. So long as any cell
other than the currently selected cell is clicked, the paste action will
occur, so be sure to click the correct cell the first time.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

If you want formatting and all to be pasted, just change the
Paste:=xlPasteValues to Paste:=xlPasteAll
 
Additional note to pasting from the clipboard. The paste special method
will produce an error message that "PasteSpecial method failed" if there is
nothing on the clipboard when the code runs. Therefore, the user should be
sure that they do nothing between the copy action and the paste special
action that would clear the clipboard.
 
Back
Top