Moving cursor

G

Guest

I want to include code in a macro that will allow me to move the cursor from
one cell to another. For example, if the cursor is in a cell and I want to
move it down one row, what is the code. The cell that the cursor is in will
vary and will typically be the first blank cell at the bottom of the sheet.

What I am doing is copying rows from one sheet to another. I select the rows
from one sheet and then run the macro. I want the macro to goto the other
sheet, find the first blank row, paste the rows from the original sheet and
return to the original sheet in order for me to select the next number of
rows to copy.

The actual macro I need is more sophisticated than what I am describing, but
for now knowing how to accomplish the above will be most helpful.
 
D

Don Guillett

You rarely have to select anything to get the job done. to get the last row
lr=sheets("othersheet").cells(rows.count,"a").end(xlup).row

you would want to put that into a for/next loop
 
G

Guest

I am not having a problem finding the last row but I need to enter data into
a cell that is located one row below the "last row" that I found utilizing
Ctrl-End. This will create a new last row and will be the row that the copied
data will be posted into.

Unfortunately I am not a VBA programmer and am having to create shortcuts to
do a lot of what I am given to do at work. I am going to have to work with 8
different workbooks and many thousands of records and create additional
sheets that will contain data from the original sheets. This data is being
copied and pasted to the new sheets in order to populate several tables in
Access.


Basically my workbooks will contain data like:

CustName CustNumb Doc AcctNumb
Zhenzhen Liu 993317795 Non-Doc 173359236
Zhi Hong Xie 993995996 Non-Doc
993995996 175783112
Zhihua Xu 993031874 Non-Doc
993031874 173078900;
993031874 170387351
Zhong Du 991448158 Non-Doc

I have to copy the rows that contain the CustNumb and AcctNumb data and
paste them into a new sheet, AcctNumb. Then I need to delete the rows in the
main sheet that I just copied and move on to the the next CustNumb/AcctNumb
set. The data copied to the AcctNumb sheet will then be imported into the
AcctNumb table in Access.

If I had the knowledge you and Ron de Bruin had this would be a piece of
cake. Unfortunately my employer is not going to give me the time to take the
courses I need to write code.

Thank you for your help!!
 
G

Guest

Ron,

I used the following code from your suggestion:

Sub MoveRows()
'
' MoveRows Macro
' Macro recorded 4/7/2006 by TMP1020
'
' Keyboard Shortcut: Ctrl+Shift+Z
'
Dim Lr As Long
Application.ScreenUpdating = False
Selection.Copy
Sheets("AcctNumb").Select
Lr = LastRow(Sheets("AcctNumb")) + 1
Set destrange = Sheets("AcctNumb").Rows(Lr)
destrange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
Application.ScreenUpdating = True
Sheets("NRA'S").Select

End Sub


So far it seems to work fine. Now all I need is a way to automate the macro
so that it selects the rows I want to copy and paste so that I don't have to
select them for each customer. I included a sample of one of my workbooks in
my reply to Ron Guillett in case you would like to see it. There is more data
on the first row of each customer but the other rows only contain customer
number and customer account numbers.


I will look into your other suggestion as well. Thank you.
You guys are really helpful! I read a lot of your replys to others as well.

All suggestions are helpful to me and I thank you!!

David
 
G

Guest

The row with the customer name in column A is going to stay in the "master"
sheet. The lines following each customer name (up to the next customer name)
need to be copied to a different sheet and then deleted from the master sheet.

What I will end up with is one sheet with customer information, and one
sheet with customer account numbers. The link, of course, is the customer
number which is repeated on both sheets. These two sheets will be imported
into an Access database utilizing 2 tables: customer information and account
information.

I will be doing similar exercises on 8 different workbooks some of which
have over 10-20 thousand rows.

I am not sure I am up to the task but with your help and others I think I
can make it happen.

Why can I delete those two rows? I need to go between both of these sheets
to first highlight the rows I need to copy and then to the other sheet to
paste it. Then back to the first sheet (NRA's). It's not that I don't believe
you I just don't understand why I can do that and it still work. I'll go try
it however.

Thank you.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top