Creating a macro to paste in a specific cell

[Carlos]

Hi,

I'm trying to work out if it's possible to create a macro that will find a
cell from one workbook and identify that as the "range" cell so it can paste
information into relative cells.

For example. I have this information being updated daily.

A1 B1 C1 etc
15/01/2008 data 1 Data 2 etc

And I want the a macro that will search another workbook to find the Date
15/01/2008 and make this the range cell so that I can copy and paste B1, C1
etc into the next cells.?

Can this be done?

Thanks
Carl

 

[Dude Ranch]

Hey Carlos
This sould pretty stright forward and a good one to learn on.
And as always with Excel there is many ways to skin the cat.

Your Macro need merely hold the value to look up 15/01/2008 in yr eg.
Then activate the other workbook, systematically search an area for your
lookup value.
You will now have a location to copy to it is a simple step to code the
copy.

Have fun

Aloha
Jeff

 

[Carlos]

Hi Jeff,

Thanks for the quick reply, What I should have said was that Cell A1 (the
date) is a variable. So next day it will have the following day's date, which
it needs to search for and make the range cell.

So in the programming it's got when it's a constant is

Cells.Find(What:="Carl", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

(I've changed find with my name for ease) But what I would like it to do is
search for the text that would be in cell A1 rather then a constant text?

Is this possible?

Thanks again
Carl

 

[Dude Ranch]

Carl (os)
My friend you have DONE all the hard work.

merely replace the "Carl" in your code with a variable say find_me

then before this line executes add
Find_me=range("A1").value

or code that effect (make sure you are in the workbook first though)

You sound close keep going.

Aloha
Jeff

 

[Carlos]

Jeff,

Thank you very much.. It's been bugging me all morning.. But works a treat
now..

I've only really started working with excel and macros the last few days to
create something for work, But it's so facinating.. And the things I've
learnt to do allready is brilliant!!

Thanks again for your help
Carl