Make macro go to first empty cell in column?

[TiggerTwo]

HI, I'm really glad to see this forum. cool!
I am working on trying to getmyu macro to run.
I'm using Office for MAC on panther.

Situation: I highlight manually a certain bunch of cells.
A) I want to hit a macro key to then copy that range of cells and
B) go into another workfile and
C) past that range of cells into the first empty cell it finds under
all the other filled cells, in a certain column.

So far I can get it to do A and B. In my attempts to get it to do C I
tried clicking on the first empty cell (hoping that magically it coul
read my mind I guess to interpret it to mean to look for the first
empty cell) and that works. BUT it only works for that cell. SO that
whenever I am in teh first workbook and want to copy another different
range of cells to teh second workbook, the macro as I currently have it
set up, will copy the range and move it to that same cell whether it is
full or not, in the second workbook. It (I?) don't know how to make it
look for the next nearest empty cell in the column going down and
paste it there instead of ontop of the same one all the time..

Here's the code I have so far:

Sub movetobizsheet()
End Sub
Sub copytobiz()
'
' copytobiz Macro
' Macro recorded 1/27/2004 by Barb
'
' Keyboard Shortcut: Option+Cmd+g
'
Selection.Copy
Windows("biz2004.xls").Activate
Sheets("DOME").Select
Range("I21").Select
ActiveSheet.Paste
End Sub

I tried typing in "EmptyCell" for the Range but that didnt' work and I
got an error. Right now it only pastes into cell I21. but I want ti to
look for the nearest empty cell and paste it there.

Thansk
regards
BArb

 

[John Wilson]

TiggerTwo,

In place of:
Range("I21").Select

Try this:
Range("I" & rows.Count).end(xlup).Offset(1,0).Select

John

 

[Tom Ogilvy]

Sub copytobiz()
'
' copytobiz Macro
' Macro recorded 1/27/2004 by Barb
'
' Keyboard Shortcut: Option+Cmd+g
'
Selection.Copy
Windows("biz2004.xls").Activate
Sheets("DOME").Select
Cells(rows.count,"I").End(xlup)(2).Select
ActiveSheet.Paste
End Sub

or
Sub copytobiz()
Selection.Copy Destination:=Workbooks("biz2004.xls") _
.Worksheets("DOME").Cells(rows.count,"I").End(xlup)(2)
End Sub

If column I is completely blank, it will paste into I2. If this is a
problem, you can test for I1 being blank.

Selection.Copy
Windows("biz2004.xls").Activate
Sheets("DOME").Select
Cells(rows.count,"I").End(xlup)(2).Select
if ActiveCell.Row = 2 then
if isempty(Range("I1")) then Range("I1").Select
End if
ActiveSheet.Paste

 

[John Wilson]

Tom,

Cells(rows.count,"I")

Haven't seen it quite that way before, but it makes sense.

.End(xlup)

Okay, that's a no brainer.

(2)
Where in the heck did that come from????

Thanx,
John

Sub copytobiz()
'
' copytobiz Macro
' Macro recorded 1/27/2004 by Barb
'
' Keyboard Shortcut: Option+Cmd+g
'
Selection.Copy
Windows("biz2004.xls").Activate
Sheets("DOME").Select
Cells(rows.count,"I").End(xlup)(2).Select
ActiveSheet.Paste
End Sub

or
Sub copytobiz()
Selection.Copy Destination:=Workbooks("biz2004.xls") _
.Worksheets("DOME").Cells(rows.count,"I").End(xlup)(2)
End Sub

If column I is completely blank, it will paste into I2. If this is a
problem, you can test for I1 being blank.

Selection.Copy
Windows("biz2004.xls").Activate
Sheets("DOME").Select
Cells(rows.count,"I").End(xlup)(2).Select
if ActiveCell.Row = 2 then
if isempty(Range("I1")) then Range("I1").Select
End if
ActiveSheet.Paste

 

[Tom Ogilvy]

It is a shortcut for Item which is 1 based

Cells(rows.count,"I").End(xlup).Item(2,1)

or think of it as Cells

Cells(rows.count,"I").End(xlup).cells(2,1)

--
Regards,
Tom Ogilvy

Tom,

Cells(rows.count,"I")

Haven't seen it quite that way before, but it makes sense.

.End(xlup)

Okay, that's a no brainer.

(2)
Where in the heck did that come from????

Thanx,
John

 

[TiggerTwo]

Hey! thank you soo much! It works beautifully!!! heee eheee
I ended up writing it like this:
Sub copytobiz()
'
' copytobiz Macro
' Macro recorded 1/27/2004 by Barbara Sorensen
'
' Keyboard Shortcut: Option+Cmd+g
Selection.Copy
Windows("Biz2004.xls").Activate
Sheets("DOME").Select
Cells(Rows.Count, "I").End(xlUp)(2).Select
If ActiveCell.Row = 2 Then
If IsEmpty(Range("I1")) Then Range("I1").Select
End If
ActiveSheet.Paste
End Sub



Now......as I was playing with this, it came to mind that I might like
to make this automatic......
(I know, hindsight is awesome...)
i.e.
every time I enter a number that is 600 or greater in column E, and
greater than 0$ in column F, that I'd like it to automatically kick in
without me having to push command - option-g. How can I do that?
And...also, if I later change the amount or number in the first
worksheet, I woudl like it to automatically change the amount or number
int eh second sheet as well. I'm guessing it has something to do with
if/then and sort? Can an excell sheet be made to trigger automatically
like that? or am I asking for greater trouble down the line?

thanks
Barb