copy cell from one workbook to another

[Larry]

Hi Folks,
I sure could use some help with this. I have been trying to do some code
without enough experience.
I have a workbook (#1) that has one sheet and it generates a sequential
number each time it is opened. I use the number in another workbook(#2) to
identify new items entered into a list.
I am trying to make it so the selected cell in workbook 2 will open a
hyperlink to or just open workbook 1 and copy the number from the active cell
where the new number was just created and paste it into the selected cell of
Workbook2 and then close workbook 1. I have tried several things by
researching what you all post here but, alas I am too novice for this level
of code. Thanks in advance!! larry



Sub waiverNo()
Range("D").Select.ActiveCell
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Selection.Copy
Windows("1986.xls").Activate
ActiveSheet.Paste
Windows("WAIVER NO.xls").Activate
ActiveWindow.Close
End Sub

 

[Duke Carey]

Why wouldn't you just use something like


activecell.Value = evaluate("=1+max(e:e)")

 

[Larry]

Duke, Thanks for the reply. I really am a novice and could use the help.
I tried pasting this line into a workbook module(module1)
ActiveCell.Value = Evaluate("=1+max(d:d)")
d is the colummn I need to place the info in.
I am getting an "invalid outside procedure" error for this part of the code:
"=1+max(d:d)"

It does go and open the source workbook but does not seem to be able to
place the info I need into the target worksheet cell.
more help please?? thanks, larry

 

[Duke Carey]

The formula assumes you are looking to add one to the max value ON THE TARGET
WORKSHEET. I offered it as an alternative to opening the first workbook that
simply increments a number. So..if you are pasting this incrementing number
into column D, then this code will work when the TARGET worksheet is active -
and you've selected the cell into which the # goes.

As a general rule, you rarely need to SELECT a cell to modify it:

Range("D33").value = evaluate("=1+max(d:d)")

will set cell D33 to 1 higher than the max # in column D

Hope this helps

 

[Larry]

Duke,
Thanks, I think this is exactly what I wanted, I just haven't figured out
where to put it. I have tried adding it to a worksheet code by itself, into
other code, to a module1 and to the this workbook sheet as weel as in the
formula line of column e selecting all cells. I clearly do not know what I am
to do with this eloquent and simple tool. A little more help, like spell it
out for me thanks for being there, larry

 

[Larry]

Duke,
I thought about this a little more and realize that while this is cool, I
did not fully explain y situation. I am attempting to replace an existing 5X8
paper card system and there are already many numbers from 1 to 900 assigned
to various sheets in various workbooks. each sheet representing a different
auto. There are many numbers on each sheet and geting the max+1 would not
necessarily give me the single next higher number. I think I am back to
wondering how to go to the other workbook and importing the newlygenerated
number just by clicking in the target cell.
The source workbook is Waiver No.Xls with only ws#1 "new numbers".
The representative target workbook(of 18) is ex "1986" with ws#1 "Lists" and
a varing numbers of sheets for autos.
On the worksheet for each is AB= hidden C= descrepency(with a dynamic
dropdown) D with New waiver number(the target cells) E = todays date and F =
Initials (static drop down) G-IV = hidden
So, think your up to helping figure a way to get that new number into the
target active cell?
Sure appreciate that you even take the time. Take care, larry