Macro to move one cell down
 

Instead of "Range("A4").Select" from the code below I want the code to just
go one cell down until it finds a blank cell. This is a macro that would be
run one at a time and not on a loop. For example, the user will click a
command button, the code would run once (unless it finds an empty cell) and
then end until the user clicks the command button again. Obviously this is
very possible but I'm at a loss for the code. Thanks all!

Windows("Extract.xls").Activate
Range("A4").Select
Selection.Copy
Windows("PascoTOC.xls").Activate
Range("E8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Macro to move one cell down
 

Try

Windows("Extract.xls").Activate
Range("A65536").End(xlUp).Offset(1,0).Select
Selection.Copy
Windows("PascoTOC.xls").Activate
Range("E8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

"tjb" wrote:

Instead of "Range("A4").Select" from the code below I want the code to just
go one cell down until it finds a blank cell. This is a macro that would be
run one at a time and not on a loop. For example, the user will click a
command button, the code would run once (unless it finds an empty cell) and
then end until the user clicks the command button again. Obviously this is
very possible but I'm at a loss for the code. Thanks all!

Windows("Extract.xls").Activate
Range("A4").Select
Selection.Copy
Windows("PascoTOC.xls").Activate
Range("E8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Macro to move one cell down
 

That didn't quite work and I'm not sure I follow what you were trying to do
with the code.

"akphidelt" wrote:

Try

Windows("Extract.xls").Activate
Range("A65536").End(xlUp).Offset(1,0).Select
Selection.Copy
Windows("PascoTOC.xls").Activate
Range("E8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

"tjb" wrote:

Instead of "Range("A4").Select" from the code below I want the code to just
go one cell down until it finds a blank cell. This is a macro that would be
run one at a time and not on a loop. For example, the user will click a
command button, the code would run once (unless it finds an empty cell) and
then end until the user clicks the command button again. Obviously this is
very possible but I'm at a loss for the code. Thanks all!

Windows("Extract.xls").Activate
Range("A4").Select
Selection.Copy
Windows("PascoTOC.xls").Activate
Range("E8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Macro to move one cell down
 

Alright, I should have looked at the code harder. I was getting the first
blank cell available when what you want is the last cell that was updated. So
just use

Range("A65536").End(xlUp).Select

And it should work.

"tjb" wrote:

That didn't quite work and I'm not sure I follow what you were trying to do
with the code.

"akphidelt" wrote:

Try

Windows("Extract.xls").Activate
Range("A65536").End(xlUp).Offset(1,0).Select
Selection.Copy
Windows("PascoTOC.xls").Activate
Range("E8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

"tjb" wrote:

Instead of "Range("A4").Select" from the code below I want the code to just
go one cell down until it finds a blank cell. This is a macro that would be
run one at a time and not on a loop. For example, the user will click a
command button, the code would run once (unless it finds an empty cell) and
then end until the user clicks the command button again. Obviously this is
very possible but I'm at a loss for the code. Thanks all!

Windows("Extract.xls").Activate
Range("A4").Select
Selection.Copy
Windows("PascoTOC.xls").Activate
Range("E8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Macro to move one cell down
 

Still no go. To be clear, I have data in cells A1:A356. When the user
clicks the command button, I want the macro to copy cell A1 and insert in the
appropriate workbook. When the user clicks the command button again, I want
the macro to copy cell A2 and so on until it sees no data in cell A357.
Thanks for working on this for me!

"akphidelt" wrote:

Alright, I should have looked at the code harder. I was getting the first
blank cell available when what you want is the last cell that was updated. So
just use

Range("A65536").End(xlUp).Select

And it should work.

"tjb" wrote:

That didn't quite work and I'm not sure I follow what you were trying to do
with the code.

"akphidelt" wrote:

Try

Windows("Extract.xls").Activate
Range("A65536").End(xlUp).Offset(1,0).Select
Selection.Copy
Windows("PascoTOC.xls").Activate
Range("E8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

"tjb" wrote:

Instead of "Range("A4").Select" from the code below I want the code to just
go one cell down until it finds a blank cell. This is a macro that would be
run one at a time and not on a loop. For example, the user will click a
command button, the code would run once (unless it finds an empty cell) and
then end until the user clicks the command button again. Obviously this is
very possible but I'm at a loss for the code. Thanks all!

Windows("Extract.xls").Activate
Range("A4").Select
Selection.Copy
Windows("PascoTOC.xls").Activate
Range("E8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Macro to move one cell down
 

Oh wow, thats completely different then I imagined. So is there criteria that
has to be met to determine what workbook to go to or are you just copying all
the data from A1:A356 to another workbook?

To get a dynamic range you use

Range(Range("A1"),Range("A1").End(xlDown)).Select

This will select the entire range as long as it has data in column A
regardless of the amount of rows.

"tjb" wrote:

Still no go. To be clear, I have data in cells A1:A356. When the user
clicks the command button, I want the macro to copy cell A1 and insert in the
appropriate workbook. When the user clicks the command button again, I want
the macro to copy cell A2 and so on until it sees no data in cell A357.
Thanks for working on this for me!

"akphidelt" wrote:

Alright, I should have looked at the code harder. I was getting the first
blank cell available when what you want is the last cell that was updated. So
just use

Range("A65536").End(xlUp).Select

And it should work.

"tjb" wrote:

That didn't quite work and I'm not sure I follow what you were trying to do
with the code.

"akphidelt" wrote:

Try

Windows("Extract.xls").Activate
Range("A65536").End(xlUp).Offset(1,0).Select
Selection.Copy
Windows("PascoTOC.xls").Activate
Range("E8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

"tjb" wrote:

Instead of "Range("A4").Select" from the code below I want the code to just
go one cell down until it finds a blank cell. This is a macro that would be
run one at a time and not on a loop. For example, the user will click a
command button, the code would run once (unless it finds an empty cell) and
then end until the user clicks the command button again. Obviously this is
very possible but I'm at a loss for the code. Thanks all!

Windows("Extract.xls").Activate
Range("A4").Select
Selection.Copy
Windows("PascoTOC.xls").Activate
Range("E8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Macro to move one cell down
 

Oops - forgot to test for a blank cell - sorry. Included below.

Windows("Extract.xls").Activate
Static A As Integer
If A = 0 Then A = 4
If Cells(A, 1) = "" Then Exit Sub
Cells(A, 1).Select
A = A + 1
Selection.Copy
Windows("PascoTOC.xls").Activate
Range("E8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Regards - Dave.

Macro to move one cell down
 

Hi tjb,
You can use the Cells property.
So your code will look like this:

Windows("Extract.xls").Activate
Static A As Integer
If A = 0 Then A = 4
Cells(A, 1).Select
A = A + 1
Selection.Copy
Windows("PascoTOC.xls").Activate
Range("E8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Each time the macro is run, the variable 'A' is incremented, so the Cells(A,
1) will refer to the next cell in the column. (The Cells property uses R1C1
notation).
Declaring 'A' as Static, means that its value will be retained for as long
as the workbook is open. If you close the workbook, the macro will start at
A4 again.
Hope this is what you're after.
Dave.

Macro to move one cell down
 

I'm not selecting a range, just the contents of the cell selected after
moving down one until blank.

"akphidelt" wrote:

Oh wow, thats completely different then I imagined. So is there criteria that
has to be met to determine what workbook to go to or are you just copying all
the data from A1:A356 to another workbook?

To get a dynamic range you use

Range(Range("A1"),Range("A1").End(xlDown)).Select

This will select the entire range as long as it has data in column A
regardless of the amount of rows.

"tjb" wrote:

Still no go. To be clear, I have data in cells A1:A356. When the user
clicks the command button, I want the macro to copy cell A1 and insert in the
appropriate workbook. When the user clicks the command button again, I want
the macro to copy cell A2 and so on until it sees no data in cell A357.
Thanks for working on this for me!

"akphidelt" wrote:

Alright, I should have looked at the code harder. I was getting the first
blank cell available when what you want is the last cell that was updated. So
just use

Range("A65536").End(xlUp).Select

And it should work.

"tjb" wrote:

That didn't quite work and I'm not sure I follow what you were trying to do
with the code.

"akphidelt" wrote:

Try

Windows("Extract.xls").Activate
Range("A65536").End(xlUp).Offset(1,0).Select
Selection.Copy
Windows("PascoTOC.xls").Activate
Range("E8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

"tjb" wrote:

Instead of "Range("A4").Select" from the code below I want the code to just
go one cell down until it finds a blank cell. This is a macro that would be
run one at a time and not on a loop. For example, the user will click a
command button, the code would run once (unless it finds an empty cell) and
then end until the user clicks the command button again. Obviously this is
very possible but I'm at a loss for the code. Thanks all!

Windows("Extract.xls").Activate
Range("A4").Select
Selection.Copy
Windows("PascoTOC.xls").Activate
Range("E8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Macro to move one cell down
 

The value of the cell is text, not an integer so this code is not copying the
text of the cell to paste into the other workbook

"Dave" wrote:

Oops - forgot to test for a blank cell - sorry. Included below.

Windows("Extract.xls").Activate
Static A As Integer
If A = 0 Then A = 4
If Cells(A, 1) = "" Then Exit Sub
Cells(A, 1).Select
A = A + 1
Selection.Copy
Windows("PascoTOC.xls").Activate
Range("E8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Regards - Dave.

Macro to move one cell down
 

Hi,
I didn't check the rest of your code. I assumed it was working all except
for the moving to a new cell thing.
XL's copy/paste action doesn't care about the contents of a cell, whether
text or integer, or anything else.
When you select Range ("A8") from the Extract.xls workbook, there is no
worksheet specified. Does that workbook only have one sheet?
What actually happens when you run the macro?
Dave.

Macro to move one cell down
 

I think this may be getting too complicated so maybe I can make this even
simpler. All I really need is the line of code that moves the active cell
from A1 to A2, then A2 to A3, A3 to A4 and so on. Thanks for your patience
on this, I really appreciate it!

"Dave" wrote:

Hi,
I didn't check the rest of your code. I assumed it was working all except
for the moving to a new cell thing.
XL's copy/paste action doesn't care about the contents of a cell, whether
text or integer, or anything else.
When you select Range ("A8") from the Extract.xls workbook, there is no
worksheet specified. Does that workbook only have one sheet?
What actually happens when you run the macro?
Dave.

Macro to move one cell down
 

This is making absolutely no sense. What you are saying is you want the last
cell that was inputed... the line

Range("A65536").End(xlUp).Select

will choose only one cell and will choose the last cell in column A that has
data in it. So regardless of how many extra lines you put in, it will choose
the last cell. Tell you what, go down to Cell A65536 and press CTRL + Up
Arrow. That's all that code is doing.

I'm completely lost in what you are trying to do with having a user click a
command button and have it go from A1 to A2. Do you want it to do that every
time? If so you are going to have to figure out some way to create a variable
that knows when you want to start over or a static variable that remembers
what the last row you copied was.

"tjb" wrote:

I'm not selecting a range, just the contents of the cell selected after
moving down one until blank.

"akphidelt" wrote:

Oh wow, thats completely different then I imagined. So is there criteria that
has to be met to determine what workbook to go to or are you just copying all
the data from A1:A356 to another workbook?

To get a dynamic range you use

Range(Range("A1"),Range("A1").End(xlDown)).Select

This will select the entire range as long as it has data in column A
regardless of the amount of rows.

"tjb" wrote:

Still no go. To be clear, I have data in cells A1:A356. When the user
clicks the command button, I want the macro to copy cell A1 and insert in the
appropriate workbook. When the user clicks the command button again, I want
the macro to copy cell A2 and so on until it sees no data in cell A357.
Thanks for working on this for me!

"akphidelt" wrote:

Alright, I should have looked at the code harder. I was getting the first
blank cell available when what you want is the last cell that was updated. So
just use

Range("A65536").End(xlUp).Select

And it should work.

"tjb" wrote:

That didn't quite work and I'm not sure I follow what you were trying to do
with the code.

"akphidelt" wrote:

Try

Windows("Extract.xls").Activate
Range("A65536").End(xlUp).Offset(1,0).Select
Selection.Copy
Windows("PascoTOC.xls").Activate
Range("E8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

"tjb" wrote:

Instead of "Range("A4").Select" from the code below I want the code to just
go one cell down until it finds a blank cell. This is a macro that would be
run one at a time and not on a loop. For example, the user will click a
command button, the code would run once (unless it finds an empty cell) and
then end until the user clicks the command button again. Obviously this is
very possible but I'm at a loss for the code. Thanks all!

Windows("Extract.xls").Activate
Range("A4").Select
Selection.Copy
Windows("PascoTOC.xls").Activate
Range("E8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Macro to move one cell down
 

OK

Sub MoveDown()
Static RowNo As Integer
If RowNo = 0 Then RowNo = 1
Cells(RowNo, 1).Select
RowNo = RowNo + 1
End Sub

Copy this into a module in the VBA window. Each time it is run, the active
cell will move down one row, starting in A1.

If this does what you want, remove the first and last lines, and place the
code into your own macro.

Hope this works.

Regards - Dave.

"tjb" wrote:

I think this may be getting too complicated so maybe I can make this even
simpler. All I really need is the line of code that moves the active cell
from A1 to A2, then A2 to A3, A3 to A4 and so on. Thanks for your patience
on this, I really appreciate it!

"Dave" wrote:

Hi,
I didn't check the rest of your code. I assumed it was working all except
for the moving to a new cell thing.
XL's copy/paste action doesn't care about the contents of a cell, whether
text or integer, or anything else.
When you select Range ("A8") from the Extract.xls workbook, there is no
worksheet specified. Does that workbook only have one sheet?
What actually happens when you run the macro?
Dave.