Moving cursor

[David Vollmer]

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.

[Ron de Bruin]

hi David

Look here
http://www.rondebruin.nl/copy1.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"David Vollmer" <[email protected]> wrote in message news:[email protected]...
>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.

[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



--
Don Guillett
SalesAid Software
[email protected]
"David Vollmer" <[email protected]> wrote in message
news:[email protected]...
>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.

[David Vollmer]

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!!

"Don Guillett" wrote:

> 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
>
>
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "David Vollmer" <[email protected]> wrote in message
> news:[email protected]...
> >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.
>
>
>

[Ron de Bruin]

Maybe you can use EasyFilter to copy to a new sheet ?
No code needed then

http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"David Vollmer" <[email protected]> wrote in message news:[email protected]...
>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!!
>
> "Don Guillett" wrote:
>
>> 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
>>
>>
>>
>> --
>> Don Guillett
>> SalesAid Software
>> [email protected]
>> "David Vollmer" <[email protected]> wrote in message
>> news:[email protected]...
>> >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.
>>
>>
>>

[David Vollmer]

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


"Ron de Bruin" wrote:

> Maybe you can use EasyFilter to copy to a new sheet ?
> No code needed then
>
> http://www.rondebruin.nl/easyfilter.htm
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "David Vollmer" <[email protected]> wrote in message news:[email protected]...
> >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!!
> >
> > "Don Guillett" wrote:
> >
> >> 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
> >>
> >>
> >>
> >> --
> >> Don Guillett
> >> SalesAid Software
> >> [email protected]
> >> "David Vollmer" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >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.
> >>
> >>
> >>
>
>
>

[Ron de Bruin]

Which rows do you want to copy ?

you can remove this two lines
> Sheets("AcctNumb").Select
> Sheets("NRA'S").Select



--
Regards Ron de Bruin
http://www.rondebruin.nl


"David Vollmer" <[email protected]> wrote in message news:[email protected]...
> 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
>
>
> "Ron de Bruin" wrote:
>
>> Maybe you can use EasyFilter to copy to a new sheet ?
>> No code needed then
>>
>> http://www.rondebruin.nl/easyfilter.htm
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>> "David Vollmer" <[email protected]> wrote in message
>> news:[email protected]...
>> >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!!
>> >
>> > "Don Guillett" wrote:
>> >
>> >> 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
>> >>
>> >>
>> >>
>> >> --
>> >> Don Guillett
>> >> SalesAid Software
>> >> [email protected]
>> >> "David Vollmer" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> >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.
>> >>
>> >>
>> >>
>>
>>
>>

[David Vollmer]

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.


"Ron de Bruin" wrote:

> Which rows do you want to copy ?
>
> you can remove this two lines
> > Sheets("AcctNumb").Select
> > Sheets("NRA'S").Select
>
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "David Vollmer" <[email protected]> wrote in message news:[email protected]...
> > 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
> >
> >
> > "Ron de Bruin" wrote:
> >
> >> Maybe you can use EasyFilter to copy to a new sheet ?
> >> No code needed then
> >>
> >> http://www.rondebruin.nl/easyfilter.htm
> >>
> >>
> >> --
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl
> >>
> >>
> >> "David Vollmer" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >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!!
> >> >
> >> > "Don Guillett" wrote:
> >> >
> >> >> 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
> >> >>
> >> >>
> >> >>
> >> >> --
> >> >> Don Guillett
> >> >> SalesAid Software
> >> >> [email protected]
> >> >> "David Vollmer" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> >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.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

[Ron de Bruin]

hi David

Look here for a possible solution to split the rows
http://www.rondebruin.nl/copy5.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl


"David Vollmer" <[email protected]> wrote in message news:[email protected]...
> 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.
>
>
> "Ron de Bruin" wrote:
>
>> Which rows do you want to copy ?
>>
>> you can remove this two lines
>> > Sheets("AcctNumb").Select
>> > Sheets("NRA'S").Select
>>
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>> "David Vollmer" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>> >
>> >
>> > "Ron de Bruin" wrote:
>> >
>> >> Maybe you can use EasyFilter to copy to a new sheet ?
>> >> No code needed then
>> >>
>> >> http://www.rondebruin.nl/easyfilter.htm
>> >>
>> >>
>> >> --
>> >> Regards Ron de Bruin
>> >> http://www.rondebruin.nl
>> >>
>> >>
>> >> "David Vollmer" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> >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!!
>> >> >
>> >> > "Don Guillett" wrote:
>> >> >
>> >> >> 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
>> >> >>
>> >> >>
>> >> >>
>> >> >> --
>> >> >> Don Guillett
>> >> >> SalesAid Software
>> >> >> [email protected]
>> >> >> "David Vollmer" <[email protected]> wrote in message
>> >> >> news:[email protected]...
>> >> >> >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.
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

[David Vollmer]

Thank you, Ron, I'll see if I can make it work.

"Ron de Bruin" wrote:

> hi David
>
> Look here for a possible solution to split the rows
> http://www.rondebruin.nl/copy5.htm
>
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "David Vollmer" <[email protected]> wrote in message news:[email protected]...
> > 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.
> >
> >
> > "Ron de Bruin" wrote:
> >
> >> Which rows do you want to copy ?
> >>
> >> you can remove this two lines
> >> > Sheets("AcctNumb").Select
> >> > Sheets("NRA'S").Select
> >>
> >>
> >>
> >> --
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl
> >>
> >>
> >> "David Vollmer" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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
> >> >
> >> >
> >> > "Ron de Bruin" wrote:
> >> >
> >> >> Maybe you can use EasyFilter to copy to a new sheet ?
> >> >> No code needed then
> >> >>
> >> >> http://www.rondebruin.nl/easyfilter.htm
> >> >>
> >> >>
> >> >> --
> >> >> Regards Ron de Bruin
> >> >> http://www.rondebruin.nl
> >> >>
> >> >>
> >> >> "David Vollmer" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> >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!!
> >> >> >
> >> >> > "Don Guillett" wrote:
> >> >> >
> >> >> >> 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
> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >> --
> >> >> >> Don Guillett
> >> >> >> SalesAid Software
> >> >> >> [email protected]
> >> >> >> "David Vollmer" <[email protected]> wrote in message
> >> >> >> news:[email protected]...
> >> >> >> >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.
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>