copy paste Macro

  • Thread starter Thread starter olark
  • Start date Start date
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??
 
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.
 
I will need the Paste to be a paste special, value.

But, I'll give this try, thanks for the headstart.
 
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).
 
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
 
Back
Top