copy paste Macro

O

olark

I was wondering if it is possible to make a macro do the following.

copy selected cell, move to another cell (same row) and copy
change worksheets
seach a column for the first copied value
moe 4 cells from the cell it had found in the search
then paste the second value

Is it possible??
 
E

Earl Kiosterud

Olark,

Yes. Put this in a regular module: (paste it from here, but watch for line
feeds).

Dim Data1, Data2

Sub Copy1()
Data1 = Selection.Value
Application.StatusBar = "Data1: " & Data1
End Sub

Sub Copy2()
Data2 = Selection.Value
Application.StatusBar = "Data1: " & Data1 & " Data2: " & Data2
End Sub

Sub Putt()
Selection.EntireRow.Find(What:=Data1, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0, 4) = Data2
Application.StatusBar = False
Data1 = ""
Data2 = ""
End Sub

Select the first cell, and run Copy1. You may want to assign it to a
keyboard shortcut (Tools - Macro - Macros - Options). Then select the
second cell and run Copy2. Then select th destination sheet, select any
cell in the desired row, and run Putt. Pronounced "put." This doesn't
actually do a paste, so you'll not get formats and other things from the
original cell. We can change that if you need it.
 
O

olark

I will need the Paste to be a paste special, value.

But, I'll give this try, thanks for the headstart.
 
E

Earl Kiosterud

Olark,

The code I gave you does pretty much the same as paste special - value. But
you can't use that command - it would paste-special right at the selection,
not four cells hence from where the first value is found. You must use the
macro (Putt).
 
O

olark

Earl, thank you very much for your help. the code worked great. I am
going to try and streamline it for the end user.

Thanks,
Ian
 
Top