I am trying to get a macro to go to the equivalent of the keystrokes end up
no matter where end up takes me. However, the record keystrokes macro
actually goes to the specific cell that end up took me when I recorded the
macro no matter where end up now takes me.

What I want the macro to do is as follows.

end, up, shift, right ctrl C, goto A25, ctrl V

In other words go up to the first cell with data, copy that cell and the one
to the right of it, and paste the copied data to row 25 columns A & B.

Hi Jim,

Is this what you ara after?

Sub test()
With ActiveSheet
.Range("A25:B25")=.Cells(65536, "A").End(xlUp).Resize(, 2)
End With
End Sub


Regards,
KL


"Jim Robinson" wrote in message
...
I am trying to get a macro to go to the equivalent of the keystrokes end up
no matter where end up takes me. However, the record keystrokes macro
actually goes to the specific cell that end up took me when I recorded the
macro no matter where end up now takes me.

What I want the macro to do is as follows.

end, up, shift, right ctrl C, goto A25, ctrl V

In other words go up to the first cell with data, copy that cell and the
one
to the right of it, and paste the copied data to row 25 columns A & B.

Thanks for the reply KL,
Not quite. First the macro runs a routine which finds the largest number in
a list (auto filter to 10 command). This works OK. Next I want to go to the
cell where that number is located and copy the contents of that cell and the
one to the right of it and then paste what I copied to another location.
The autofilter command hides all the rows in the list except the row that
contains the largest number. So, depending on the list, various rows get
hidden.
When I tried to make a "record keystrokes" macro do this, I used the end up
keys to go to the cell that contained the largest number. Then I used shift
right to highlight the two cells. Then I used ctrl V to past the copied
information elsewhere.
However, the macro didn't do the equivalent of end up. It recorded the
actual cell that end up took me to. So, when I run the macro, it goes to
that specific cell every time even though that cell may not now contain the
largest value in the list.


"KL" wrote:

Hi Jim,

Is this what you ara after?

Sub test()
With ActiveSheet
.Range("A25:B25")=.Cells(65536, "A").End(xlUp).Resize(, 2)
End With
End Sub


Regards,
KL


"Jim Robinson" wrote in message
...
I am trying to get a macro to go to the equivalent of the keystrokes end up
no matter where end up takes me. However, the record keystrokes macro
actually goes to the specific cell that end up took me when I recorded the
macro no matter where end up now takes me.

What I want the macro to do is as follows.

end, up, shift, right ctrl C, goto A25, ctrl V

In other words go up to the first cell with data, copy that cell and the
one
to the right of it, and paste the copied data to row 25 columns A & B.

Maybe this would help:

Dim rng As Range, tvalue

tvalue = Worksheetfunction.Max(Range("A:A")

Set rng = Cells.Find(tvalue)
If Not rng Is Nothing Then
MsgBox rng.Address
Else
MsgBox "Not Found"
End If

WkBk2.Sheets("InputSheet").Cells(rw,col) =
WkBk1.Sheets("MyData").Range(rng.address)

It is not always required to select anything.
The above finds the address of the cells and you can use this to copy to
another location.

--
steveB

Remove "AYN" from email to respond
"Jim Robinson" wrote in message
...
Thanks for the reply KL,
Not quite. First the macro runs a routine which finds the largest number
in
a list (auto filter to 10 command). This works OK. Next I want to go to
the
cell where that number is located and copy the contents of that cell and
the
one to the right of it and then paste what I copied to another location.
The autofilter command hides all the rows in the list except the row that
contains the largest number. So, depending on the list, various rows get
hidden.
When I tried to make a "record keystrokes" macro do this, I used the end
up
keys to go to the cell that contained the largest number. Then I used
shift
right to highlight the two cells. Then I used ctrl V to past the copied
information elsewhere.
However, the macro didn't do the equivalent of end up. It recorded the
actual cell that end up took me to. So, when I run the macro, it goes to
that specific cell every time even though that cell may not now contain
the
largest value in the list.


"KL" wrote:

Hi Jim,

Is this what you ara after?

Sub test()
With ActiveSheet
.Range("A25:B25")=.Cells(65536, "A").End(xlUp).Resize(, 2)
End With
End Sub


Regards,
KL


"Jim Robinson" wrote in message
...
I am trying to get a macro to go to the equivalent of the keystrokes end
up
no matter where end up takes me. However, the record keystrokes macro
actually goes to the specific cell that end up took me when I recorded
the
macro no matter where end up now takes me.

What I want the macro to do is as follows.

end, up, shift, right ctrl C, goto A25, ctrl V

In other words go up to the first cell with data, copy that cell and
the
one
to the right of it, and paste the copied data to row 25 columns A & B.

Thanks for the help Steve,

Works fine until it gets to the last two lines of code. I assume I should
substitute something for some of the things in these two lines, but am
stumped. The message does always show the correct cell.

Also, is there not a macro command equivalent to the end and up keys? or for
the shift right keys? or for the ctrl C command? or for the ctrl V command?


"STEVE BELL" wrote:

Maybe this would help:

Dim rng As Range, tvalue

tvalue = Worksheetfunction.Max(Range("A:A")

Set rng = Cells.Find(tvalue)
If Not rng Is Nothing Then
MsgBox rng.Address
Else
MsgBox "Not Found"
End If

WkBk2.Sheets("InputSheet").Cells(rw,col) =
WkBk1.Sheets("MyData").Range(rng.address)

It is not always required to select anything.
The above finds the address of the cells and you can use this to copy to
another location.

--
steveB

Remove "AYN" from email to respond
"Jim Robinson" wrote in message
...
Thanks for the reply KL,
Not quite. First the macro runs a routine which finds the largest number
in
a list (auto filter to 10 command). This works OK. Next I want to go to
the
cell where that number is located and copy the contents of that cell and
the
one to the right of it and then paste what I copied to another location.
The autofilter command hides all the rows in the list except the row that
contains the largest number. So, depending on the list, various rows get
hidden.
When I tried to make a "record keystrokes" macro do this, I used the end
up
keys to go to the cell that contained the largest number. Then I used
shift
right to highlight the two cells. Then I used ctrl V to past the copied
information elsewhere.
However, the macro didn't do the equivalent of end up. It recorded the
actual cell that end up took me to. So, when I run the macro, it goes to
that specific cell every time even though that cell may not now contain
the
largest value in the list.


"KL" wrote:

Hi Jim,

Is this what you ara after?

Sub test()
With ActiveSheet
.Range("A25:B25")=.Cells(65536, "A").End(xlUp).Resize(, 2)
End With
End Sub


Regards,
KL


"Jim Robinson" wrote in message
...
I am trying to get a macro to go to the equivalent of the keystrokes end
up
no matter where end up takes me. However, the record keystrokes macro
actually goes to the specific cell that end up took me when I recorded
the
macro no matter where end up now takes me.

What I want the macro to do is as follows.

end, up, shift, right ctrl C, goto A25, ctrl V

In other words go up to the first cell with data, copy that cell and
the
one
to the right of it, and paste the copied data to row 25 columns A & B.

These have to be on a single line. Note the line contiuation symbol _

WkBk2.Sheets("InputSheet").Cells(rw,col) = _
WkBk1.Sheets("MyData").Range(rng.address)

WkBk1 & WkBk2 are variable representations of 2 different workbooks
InputSheet & MyData are generic names for worksheets.
You need to change these to match your own situation.

The easiest way to capture the end up, down, sideways, etc commands is to
open a new workbook and just play with the macro recorder. Than edit the
code that is generated.

Recording is a great way to learn basic coding.

Be aware that my code is designed in such a way that there is no selecting
or activating.
When you can do that - the code runs faster.

-
steveB

Remove "AYN" from email to respond
"Jim Robinson" wrote in message
...
Thanks for the help Steve,

Works fine until it gets to the last two lines of code. I assume I should
substitute something for some of the things in these two lines, but am
stumped. The message does always show the correct cell.

Also, is there not a macro command equivalent to the end and up keys? or
for
the shift right keys? or for the ctrl C command? or for the ctrl V
command?


"STEVE BELL" wrote:

Maybe this would help:

Dim rng As Range, tvalue

tvalue = Worksheetfunction.Max(Range("A:A")

Set rng = Cells.Find(tvalue)
If Not rng Is Nothing Then
MsgBox rng.Address
Else
MsgBox "Not Found"
End If

WkBk2.Sheets("InputSheet").Cells(rw,col) =
WkBk1.Sheets("MyData").Range(rng.address)

It is not always required to select anything.
The above finds the address of the cells and you can use this to copy to
another location.

--
steveB

Remove "AYN" from email to respond
"Jim Robinson" wrote in message
...
Thanks for the reply KL,
Not quite. First the macro runs a routine which finds the largest
number
in
a list (auto filter to 10 command). This works OK. Next I want to go
to
the
cell where that number is located and copy the contents of that cell
and
the
one to the right of it and then paste what I copied to another
location.
The autofilter command hides all the rows in the list except the row
that
contains the largest number. So, depending on the list, various rows
get
hidden.
When I tried to make a "record keystrokes" macro do this, I used the
end
up
keys to go to the cell that contained the largest number. Then I used
shift
right to highlight the two cells. Then I used ctrl V to past the
copied
information elsewhere.
However, the macro didn't do the equivalent of end up. It recorded the
actual cell that end up took me to. So, when I run the macro, it goes
to
that specific cell every time even though that cell may not now contain
the
largest value in the list.


"KL" wrote:

Hi Jim,

Is this what you ara after?

Sub test()
With ActiveSheet
.Range("A25:B25")=.Cells(65536, "A").End(xlUp).Resize(, 2)
End With
End Sub


Regards,
KL


"Jim Robinson" wrote in
message
...
I am trying to get a macro to go to the equivalent of the keystrokes
end
up
no matter where end up takes me. However, the record keystrokes
macro
actually goes to the specific cell that end up took me when I
recorded
the
macro no matter where end up now takes me.

What I want the macro to do is as follows.

end, up, shift, right ctrl C, goto A25, ctrl V

In other words go up to the first cell with data, copy that cell and
the
one
to the right of it, and paste the copied data to row 25 columns A &
B.