Macro to Copy Info to the Active Cell

[mpalmer]

I am new to macros and cannot figure this out. I have a range of cells that are fixed on the worksheet. I want to click on a blank cell - then use a macsro to go to the fixed range - copy the range and paste it back to blank cell (active) that i started in. Sure its easy for you guys but its tearing me up! Thanks in advance for any suggestions....

 

[DonkeyOte]

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim rng As Range
Set rng = Sheets("Sheet1").Range(Cells(1, 1), Cells(1, 5)) 'your fixed range (here A1:A5)
With rng
    .Copy
    Target.PasteSpecial xlPasteValues
End With
Application.CutCopyMode = False
End Sub

Assumes you're pasting data into the same sheet on which the fixed data is stored

It will also fire everytime you double click on a cell -- if you want a fail-safe you could put a msgbox into the code before activating the copy / paste routine to get confirmation from end-user ... eg

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
If MsgBox("Copy Range?",vbyesno,"Copy Confirm") = vbno then exit sub
Dim rng As Range
Set rng = Sheets("Sheet1").Range(Cells(1, 1), Cells(1, 5)) 'your fixed range (here A1:A5)
With rng
    .Copy
    Target.PasteSpecial xlPasteValues
End With
Application.CutCopyMode = False
End Sub

 

[mpalmer]

Thats great - I will give it shot- Thank You!!!!!