Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
tjb tjb is offline
external usenet poster
 
Posts: 14
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 461
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
tjb tjb is offline
external usenet poster
 
Posts: 14
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 461
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
tjb tjb is offline
external usenet poster
 
Posts: 14
Default 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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 461
Default 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

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,388
Default 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.
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,388
Default 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.
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
tjb tjb is offline
external usenet poster
 
Posts: 14
Default 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

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
tjb tjb is offline
external usenet poster
 
Posts: 14
Default 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.



  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,388
Default 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.
  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
tjb tjb is offline
external usenet poster
 
Posts: 14
Default 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.

  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 461
Default 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

  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,388
Default 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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
macro to move part of cell contents to another cell icetoad hisself Excel Discussion (Misc queries) 4 November 27th 06 08:19 PM
Visual basic macro move cursor down 1 cell Roger Excel Worksheet Functions 1 September 12th 06 08:09 PM
macro- move down to empty cell & active worksheet EducatingMom Excel Worksheet Functions 3 August 4th 06 11:02 PM
move to another cell within a subtotal report within a macro NoelH Excel Worksheet Functions 1 August 31st 05 03:02 PM
enter data in cell which will start macro to move data to sheet2 Tommy Excel Discussion (Misc queries) 0 May 12th 05 05:00 PM


All times are GMT +1. The time now is 02:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"