Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Optitron
 
Posts: n/a
Default Copy buttons by rows


I'm learning Excel by myself and I've run into something I can't figure
out. I have a button at the end of a row that takes 4 seperate cells
from that row and puts them in 4 seperate cells on another sheet. I
want to be able to make 1000 buttons that will be at the end of each
row that takes the data from that row and puts it on another sheet just
so I can print it, but I don't want to make them one at a time. Unless
there is another way....


--
Optitron
------------------------------------------------------------------------
Optitron's Profile: http://www.excelforum.com/member.php...o&userid=26729
View this thread: http://www.excelforum.com/showthread...hreadid=399813

  #2   Report Post  
Dave Peterson
 
Posts: n/a
Default

I think I'd put the button in row 1 (shift the other stuff down).

Then window|freeze panes so that row 1 is always visible.

And modify the macro to use the row that has the activecell.



Optitron wrote:

I'm learning Excel by myself and I've run into something I can't figure
out. I have a button at the end of a row that takes 4 seperate cells
from that row and puts them in 4 seperate cells on another sheet. I
want to be able to make 1000 buttons that will be at the end of each
row that takes the data from that row and puts it on another sheet just
so I can print it, but I don't want to make them one at a time. Unless
there is another way....

--
Optitron
------------------------------------------------------------------------
Optitron's Profile: http://www.excelforum.com/member.php...o&userid=26729
View this thread: http://www.excelforum.com/showthread...hreadid=399813


--

Dave Peterson
  #3   Report Post  
Optitron
 
Posts: n/a
Default


OK, I froze the button. Now how do I modify the macro? I'm trying to
program this sheet so that anyone can use it, even a Marine.


--
Optitron
------------------------------------------------------------------------
Optitron's Profile: http://www.excelforum.com/member.php...o&userid=26729
View this thread: http://www.excelforum.com/showthread...hreadid=399813

  #4   Report Post  
bigwheel
 
Posts: n/a
Default

Or, perhaps, allow the user to select several rows and have the macro act on
all the selected.

"Dave Peterson" wrote:

I think I'd put the button in row 1 (shift the other stuff down).

Then window|freeze panes so that row 1 is always visible.

And modify the macro to use the row that has the activecell.



Optitron wrote:

I'm learning Excel by myself and I've run into something I can't figure
out. I have a button at the end of a row that takes 4 seperate cells
from that row and puts them in 4 seperate cells on another sheet. I
want to be able to make 1000 buttons that will be at the end of each
row that takes the data from that row and puts it on another sheet just
so I can print it, but I don't want to make them one at a time. Unless
there is another way....

--
Optitron
------------------------------------------------------------------------
Optitron's Profile: http://www.excelforum.com/member.php...o&userid=26729
View this thread: http://www.excelforum.com/showthread...hreadid=399813


--

Dave Peterson

  #5   Report Post  
Optitron
 
Posts: n/a
Default


I need to know how to do these things you are suggesting please.


bigwheel Wrote:
Or, perhaps, allow the user to select several rows and have the macro
act on
all the selected.

"Dave Peterson" wrote:

I think I'd put the button in row 1 (shift the other stuff down).

Then window|freeze panes so that row 1 is always visible.

And modify the macro to use the row that has the activecell.



Optitron wrote:

I'm learning Excel by myself and I've run into something I can't

figure
out. I have a button at the end of a row that takes 4 seperate

cells
from that row and puts them in 4 seperate cells on another sheet.

I
want to be able to make 1000 buttons that will be at the end of

each
row that takes the data from that row and puts it on another sheet

just
so I can print it, but I don't want to make them one at a time.

Unless
there is another way....

--
Optitron

------------------------------------------------------------------------
Optitron's Profile:

http://www.excelforum.com/member.php...o&userid=26729
View this thread:

http://www.excelforum.com/showthread...hreadid=399813

--

Dave Peterson



--
Optitron
------------------------------------------------------------------------
Optitron's Profile: http://www.excelforum.com/member.php...o&userid=26729
View this thread: http://www.excelforum.com/showthread...hreadid=399813



  #6   Report Post  
bigwheel
 
Posts: n/a
Default

What have you got so far with your single button at the end of the row?

"Optitron" wrote:


I need to know how to do these things you are suggesting please.


  #7   Report Post  
Dave Peterson
 
Posts: n/a
Default

Do you take the data from each row, populate the other worksheet, then print,
then get the next row and repeat?

If yes, then maybe selecting all the rows you want to use makes more sense??

And I made some assumptions--sheet names, cell addresses and the like:

Option Explicit
Sub testme()
Dim prtWks As Worksheet
Dim actWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim iRow As Long

Set actWks = ActiveSheet
Set prtWks = Worksheets("Sheet2")

Set myRng = Intersect(Selection.EntireRow, actWks.Range("a:a"))

With prtWks
For Each myCell In myRng.Cells
iRow = myCell.Row
.Range("a1").Value = actWks.Cells(iRow, "b").Value
.Range("c99").Value = actWks.Cells(iRow, "x").Value
.Range("b3").Value = actWks.Cells(iRow, "C").Value
.Range("d4").Value = actWks.Cells(iRow, "A").Value
Application.Calculate
.PrintOut preview:=True
Next myCell
End With
End Sub

I put the info in column B into A1
X into C99
C into B3
A into D4

I also called the sheet to be printed Sheet2.

Change those things and test it out.

When/if you're happy, get rid of the preview:=true portion. (That's just to
save some trees!)

Optitron wrote:

OK, I froze the button. Now how do I modify the macro? I'm trying to
program this sheet so that anyone can use it, even a Marine.

--
Optitron
------------------------------------------------------------------------
Optitron's Profile: http://www.excelforum.com/member.php...o&userid=26729
View this thread: http://www.excelforum.com/showthread...hreadid=399813


--

Dave Peterson
  #8   Report Post  
Optitron
 
Posts: n/a
Default


All I have is single buttons that I have to record a macro for each
time. A thousand buttons will take me a week to record. I need a
quicker, easier way.

bigwheel Wrote:
What have you got so far with your single button at the end of the row?

"Optitron" wrote:


I need to know how to do these things you are suggesting please.



--
Optitron
------------------------------------------------------------------------
Optitron's Profile: http://www.excelforum.com/member.php...o&userid=26729
View this thread: http://www.excelforum.com/showthread...hreadid=399813

  #9   Report Post  
bigwheel
 
Posts: n/a
Default

I have a button at the end of a row that takes 4 seperate cells
from that row and puts them in 4 seperate cells on another sheet


What is in the macro code?

"Optitron" wrote:


All I have is single buttons that I have to record a macro for each
time. A thousand buttons will take me a week to record. I need a
quicker, easier way.

bigwheel Wrote:
What have you got so far with your single button at the end of the row?

"Optitron" wrote:


I need to know how to do these things you are suggesting please.



--
Optitron
------------------------------------------------------------------------
Optitron's Profile: http://www.excelforum.com/member.php...o&userid=26729
View this thread: http://www.excelforum.com/showthread...hreadid=399813


  #10   Report Post  
Optitron
 
Posts: n/a
Default


I copied everything and changed "sheet2" to what it was and the B, X, C,
and A back to what they were and I get an error. When I leave B, X, C,
and A the same I get a print preview of the form but it doesn't have
the info.


Dave Peterson Wrote:
Do you take the data from each row, populate the other worksheet, then
print,
then get the next row and repeat?

If yes, then maybe selecting all the rows you want to use makes more
sense??

And I made some assumptions--sheet names, cell addresses and the like:

Option Explicit
Sub testme()
Dim prtWks As Worksheet
Dim actWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim iRow As Long

Set actWks = ActiveSheet
Set prtWks = Worksheets("Sheet2")

Set myRng = Intersect(Selection.EntireRow, actWks.Range("a:a"))

With prtWks
For Each myCell In myRng.Cells
iRow = myCell.Row
.Range("a1").Value = actWks.Cells(iRow, "b").Value
.Range("c99").Value = actWks.Cells(iRow, "x").Value
.Range("b3").Value = actWks.Cells(iRow, "C").Value
.Range("d4").Value = actWks.Cells(iRow, "A").Value
Application.Calculate
.PrintOut preview:=True
Next myCell
End With
End Sub

I put the info in column B into A1
X into C99
C into B3
A into D4

I also called the sheet to be printed Sheet2.

Change those things and test it out.

When/if you're happy, get rid of the preview:=true portion. (That's
just to
save some trees!)

Optitron wrote:

OK, I froze the button. Now how do I modify the macro? I'm trying to
program this sheet so that anyone can use it, even a Marine.

--
Optitron

------------------------------------------------------------------------
Optitron's Profile:

http://www.excelforum.com/member.php...o&userid=26729
View this thread:

http://www.excelforum.com/showthread...hreadid=399813

--

Dave Peterson



--
Optitron
------------------------------------------------------------------------
Optitron's Profile: http://www.excelforum.com/member.php...o&userid=26729
View this thread: http://www.excelforum.com/showthread...hreadid=399813



  #11   Report Post  
Optitron
 
Posts: n/a
Default


I am lost now. I think it would be easier if I sent someone the workbook
and they fix it for me. I won't read this again until tomorrow.

bigwheel Wrote:
I have a button at the end of a row that takes 4 seperate cells
from that row and puts them in 4 seperate cells on another sheet


What is in the macro code?



--
Optitron
------------------------------------------------------------------------
Optitron's Profile: http://www.excelforum.com/member.php...o&userid=26729
View this thread: http://www.excelforum.com/showthread...hreadid=399813

  #12   Report Post  
Dave Peterson
 
Posts: n/a
Default

Post your code and what you want to happen.

Optitron wrote:

I copied everything and changed "sheet2" to what it was and the B, X, C,
and A back to what they were and I get an error. When I leave B, X, C,
and A the same I get a print preview of the form but it doesn't have
the info.

Dave Peterson Wrote:
Do you take the data from each row, populate the other worksheet, then
print,
then get the next row and repeat?

If yes, then maybe selecting all the rows you want to use makes more
sense??

And I made some assumptions--sheet names, cell addresses and the like:

Option Explicit
Sub testme()
Dim prtWks As Worksheet
Dim actWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim iRow As Long

Set actWks = ActiveSheet
Set prtWks = Worksheets("Sheet2")

Set myRng = Intersect(Selection.EntireRow, actWks.Range("a:a"))

With prtWks
For Each myCell In myRng.Cells
iRow = myCell.Row
.Range("a1").Value = actWks.Cells(iRow, "b").Value
.Range("c99").Value = actWks.Cells(iRow, "x").Value
.Range("b3").Value = actWks.Cells(iRow, "C").Value
.Range("d4").Value = actWks.Cells(iRow, "A").Value
Application.Calculate
.PrintOut preview:=True
Next myCell
End With
End Sub

I put the info in column B into A1
X into C99
C into B3
A into D4

I also called the sheet to be printed Sheet2.

Change those things and test it out.

When/if you're happy, get rid of the preview:=true portion. (That's
just to
save some trees!)

Optitron wrote:

OK, I froze the button. Now how do I modify the macro? I'm trying to
program this sheet so that anyone can use it, even a Marine.

--
Optitron

------------------------------------------------------------------------
Optitron's Profile:

http://www.excelforum.com/member.php...o&userid=26729
View this thread:

http://www.excelforum.com/showthread...hreadid=399813

--

Dave Peterson


--
Optitron
------------------------------------------------------------------------
Optitron's Profile: http://www.excelforum.com/member.php...o&userid=26729
View this thread: http://www.excelforum.com/showthread...hreadid=399813


--

Dave Peterson
  #13   Report Post  
Optitron
 
Posts: n/a
Default


HERE IS THE CODE I STARTED WITH:

Sub BUTTON1()
'
' BUTTON1 Macro
' Macro recorded 08/29/2005 by harrisonsl
'

'
Sheets("TURN-IN DOC").Select
Range("B6").Select
ActiveCell.FormulaR1C1 = "=BTR!R9C14"
Range("D10").Select
ActiveCell.FormulaR1C1 = "=BTR!R9C1"
Range("B12:E13").Select
ActiveCell.FormulaR1C1 = "=BTR!R9C26"
Range("B17").Select
ActiveCell.FormulaR1C1 = "=BTR!R9C51"
Range("B18").Select
End Sub

HERE'S WHAT I WANT TO HAPPEN. HOPEFULLY THIS ISN'T TOO CONFUSING. I
WROTE THIS SO YOU CAN REPLICATE THIS IN YOUR OWN EXCEL.:

BUTTON1 = MOVE ROW *9* SHEET2 (BTR) CELLS TO SHEET1 (TURN-IN DOC)
CELLS

SHEET2 CELLS
CELL: A*9*|N*9*|Z*9*|AY*9*
VALUE: 05W297|310|Flashlight,Mag,2Cell,withBaton|6230-01-432-6430

SHEET1 CELLS
CELL: D10|B6|B12|B17
CELL NAME: REPORT #|WORKCENTER|NOMENCLATURE|NSN

SHEET2 A*9* TO SHEET1 D10
SHEET2 N*9* TO SHEET1 B6
SHEET2 Z*9* TO SHEET1 B12
SHEET2 AY*9* TO SHEET1 B17

BUTTON2 = MOVE ROW *10* SHEET2 (BTR) CELLS TO SHEET 1 (TURN-IN DOC)
CELLS

SHEET2 CELLS
CELL: A*10*|N*10*|Z*10*|AY*10*
VALUE: 05W297|310|Flashlight,Mag,2Cell,withBaton|6230-01-432-6430

SHEET1 CELLS
CELL: D10|B6|B12|B17
CELL NAME: REPORT #|WORKCENTER|NOMENCLATURE|NSN

SHEET2 A*10* TO SHEET1 D10
SHEET2 N*10* TO SHEET1 B6
SHEET2 Z*10* TO SHEET1 B12
SHEET2 AY*10* TO SHEET1 B17

I need this for every row without recording each macro over and over
again.

Dave Peterson Wrote:
Post your code and what you want to happen.



--
Optitron
------------------------------------------------------------------------
Optitron's Profile: http://www.excelforum.com/member.php...o&userid=26729
View this thread: http://www.excelforum.com/showthread...hreadid=399813

  #14   Report Post  
Dave Peterson
 
Posts: n/a
Default

It looks like you:

BTR -- turn-in doc
-------- -----------
column A -- D10
N -- B6
Z -- B12
AY -- B17

Do you do something with those values after you copy them to "turn-in doc"? I
did a print preview in this code:

Option Explicit
Sub testme()
Dim prtWks As Worksheet
Dim actWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim iRow As Long

Set actWks = ActiveSheet 'BTR is the one with the buttons
Set prtWks = Worksheets("TURN-IN DOC")

Set myRng = Intersect(Selection.EntireRow, actWks.Range("a:a"))

With prtWks
For Each myCell In myRng.Cells
iRow = myCell.Row
.Range("d10").Value = actWks.Cells(iRow, "a").Value
.Range("b6").Value = actWks.Cells(iRow, "n").Value
.Range("b12").Value = actWks.Cells(iRow, "z").Value
.Range("b17").Value = actWks.Cells(iRow, "ay").Value
Application.Calculate
.PrintOut preview:=True
Next myCell
End With
End Sub

If you just copy the values over, then do some manual effort, you'll only want
to get the row with the activecell.

If that's the case, you can change this line:
Set myRng = Intersect(Selection.EntireRow, actWks.Range("a:a"))
to:
Set myRng = ActiveCell

And delete that .printout line.

Optitron wrote:

HERE IS THE CODE I STARTED WITH:

Sub BUTTON1()
'
' BUTTON1 Macro
' Macro recorded 08/29/2005 by harrisonsl
'

'
Sheets("TURN-IN DOC").Select
Range("B6").Select
ActiveCell.FormulaR1C1 = "=BTR!R9C14"
Range("D10").Select
ActiveCell.FormulaR1C1 = "=BTR!R9C1"
Range("B12:E13").Select
ActiveCell.FormulaR1C1 = "=BTR!R9C26"
Range("B17").Select
ActiveCell.FormulaR1C1 = "=BTR!R9C51"
Range("B18").Select
End Sub

HERE'S WHAT I WANT TO HAPPEN. HOPEFULLY THIS ISN'T TOO CONFUSING. I
WROTE THIS SO YOU CAN REPLICATE THIS IN YOUR OWN EXCEL.:

BUTTON1 = MOVE ROW *9* SHEET2 (BTR) CELLS TO SHEET1 (TURN-IN DOC)
CELLS

SHEET2 CELLS
CELL: A*9*|N*9*|Z*9*|AY*9*
VALUE: 05W297|310|Flashlight,Mag,2Cell,withBaton|6230-01-432-6430

SHEET1 CELLS
CELL: D10|B6|B12|B17
CELL NAME: REPORT #|WORKCENTER|NOMENCLATURE|NSN

SHEET2 A*9* TO SHEET1 D10
SHEET2 N*9* TO SHEET1 B6
SHEET2 Z*9* TO SHEET1 B12
SHEET2 AY*9* TO SHEET1 B17

BUTTON2 = MOVE ROW *10* SHEET2 (BTR) CELLS TO SHEET 1 (TURN-IN DOC)
CELLS

SHEET2 CELLS
CELL: A*10*|N*10*|Z*10*|AY*10*
VALUE: 05W297|310|Flashlight,Mag,2Cell,withBaton|6230-01-432-6430

SHEET1 CELLS
CELL: D10|B6|B12|B17
CELL NAME: REPORT #|WORKCENTER|NOMENCLATURE|NSN

SHEET2 A*10* TO SHEET1 D10
SHEET2 N*10* TO SHEET1 B6
SHEET2 Z*10* TO SHEET1 B12
SHEET2 AY*10* TO SHEET1 B17

I need this for every row without recording each macro over and over
again.

Dave Peterson Wrote:
Post your code and what you want to happen.


--
Optitron
------------------------------------------------------------------------
Optitron's Profile: http://www.excelforum.com/member.php...o&userid=26729
View this thread: http://www.excelforum.com/showthread...hreadid=399813


--

Dave Peterson
  #15   Report Post  
Optitron
 
Posts: n/a
Default


It works. I'll just have to select the row and click the button. Can you
get it to just stay on the "Turn-in Doc" sheet without the print preview
each time I hit the button?

Dave Peterson Wrote:
It looks like you:

BTR -- turn-in doc
-------- -----------
column A -- D10
N -- B6
Z -- B12
AY -- B17

Do you do something with those values after you copy them to "turn-in
doc"? I
did a print preview in this code:

Option Explicit
Sub testme()
Dim prtWks As Worksheet
Dim actWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim iRow As Long

Set actWks = ActiveSheet 'BTR is the one with the buttons
Set prtWks = Worksheets("TURN-IN DOC")

Set myRng = Intersect(Selection.EntireRow, actWks.Range("a:a"))

With prtWks
For Each myCell In myRng.Cells
iRow = myCell.Row
.Range("d10").Value = actWks.Cells(iRow, "a").Value
.Range("b6").Value = actWks.Cells(iRow, "n").Value
.Range("b12").Value = actWks.Cells(iRow, "z").Value
.Range("b17").Value = actWks.Cells(iRow, "ay").Value
Application.Calculate
.PrintOut preview:=True
Next myCell
End With
End Sub

If you just copy the values over, then do some manual effort, you'll
only want
to get the row with the activecell.

If that's the case, you can change this line:
Set myRng = Intersect(Selection.EntireRow, actWks.Range("a:a"))
to:
Set myRng = ActiveCell

And delete that .printout line.

Optitron wrote:

HERE IS THE CODE I STARTED WITH:

Sub BUTTON1()
'
' BUTTON1 Macro
' Macro recorded 08/29/2005 by harrisonsl
'

'
Sheets("TURN-IN DOC").Select
Range("B6").Select
ActiveCell.FormulaR1C1 = "=BTR!R9C14"
Range("D10").Select
ActiveCell.FormulaR1C1 = "=BTR!R9C1"
Range("B12:E13").Select
ActiveCell.FormulaR1C1 = "=BTR!R9C26"
Range("B17").Select
ActiveCell.FormulaR1C1 = "=BTR!R9C51"
Range("B18").Select
End Sub

HERE'S WHAT I WANT TO HAPPEN. HOPEFULLY THIS ISN'T TOO CONFUSING. I
WROTE THIS SO YOU CAN REPLICATE THIS IN YOUR OWN EXCEL.:

BUTTON1 = MOVE ROW *9* SHEET2 (BTR) CELLS TO SHEET1 (TURN-IN DOC)
CELLS

SHEET2 CELLS
CELL: A*9*|N*9*|Z*9*|AY*9*
VALUE: 05W297|310|Flashlight,Mag,2Cell,withBaton|6230-01-432-6430

SHEET1 CELLS
CELL: D10|B6|B12|B17
CELL NAME: REPORT #|WORKCENTER|NOMENCLATURE|NSN

SHEET2 A*9* TO SHEET1 D10
SHEET2 N*9* TO SHEET1 B6
SHEET2 Z*9* TO SHEET1 B12
SHEET2 AY*9* TO SHEET1 B17

BUTTON2 = MOVE ROW *10* SHEET2 (BTR) CELLS TO SHEET 1 (TURN-IN DOC)
CELLS

SHEET2 CELLS
CELL: A*10*|N*10*|Z*10*|AY*10*
VALUE: 05W297|310|Flashlight,Mag,2Cell,withBaton|6230-01-432-6430

SHEET1 CELLS
CELL: D10|B6|B12|B17
CELL NAME: REPORT #|WORKCENTER|NOMENCLATURE|NSN

SHEET2 A*10* TO SHEET1 D10
SHEET2 N*10* TO SHEET1 B6
SHEET2 Z*10* TO SHEET1 B12
SHEET2 AY*10* TO SHEET1 B17

I need this for every row without recording each macro over and over
again.

Dave Peterson Wrote:
Post your code and what you want to happen.


--
Optitron

------------------------------------------------------------------------
Optitron's Profile:

http://www.excelforum.com/member.php...o&userid=26729
View this thread:

http://www.excelforum.com/showthread...hreadid=399813

--

Dave Peterson



--
Optitron
------------------------------------------------------------------------
Optitron's Profile: http://www.excelforum.com/member.php...o&userid=26729
View this thread: http://www.excelforum.com/showthread...hreadid=399813



  #16   Report Post  
Dave Peterson
 
Posts: n/a
Default

Instead of this:
..PrintOut preview:=True
use:
..select

Or even

application.goto .range("a1"),scroll:=true

If you want to go to a specific cell.

Optitron wrote:

It works. I'll just have to select the row and click the button. Can you
get it to just stay on the "Turn-in Doc" sheet without the print preview
each time I hit the button?

Dave Peterson Wrote:
It looks like you:

BTR -- turn-in doc
-------- -----------
column A -- D10
N -- B6
Z -- B12
AY -- B17

Do you do something with those values after you copy them to "turn-in
doc"? I
did a print preview in this code:

Option Explicit
Sub testme()
Dim prtWks As Worksheet
Dim actWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim iRow As Long

Set actWks = ActiveSheet 'BTR is the one with the buttons
Set prtWks = Worksheets("TURN-IN DOC")

Set myRng = Intersect(Selection.EntireRow, actWks.Range("a:a"))

With prtWks
For Each myCell In myRng.Cells
iRow = myCell.Row
.Range("d10").Value = actWks.Cells(iRow, "a").Value
.Range("b6").Value = actWks.Cells(iRow, "n").Value
.Range("b12").Value = actWks.Cells(iRow, "z").Value
.Range("b17").Value = actWks.Cells(iRow, "ay").Value
Application.Calculate
.PrintOut preview:=True
Next myCell
End With
End Sub

If you just copy the values over, then do some manual effort, you'll
only want
to get the row with the activecell.

If that's the case, you can change this line:
Set myRng = Intersect(Selection.EntireRow, actWks.Range("a:a"))
to:
Set myRng = ActiveCell

And delete that .printout line.

Optitron wrote:

HERE IS THE CODE I STARTED WITH:

Sub BUTTON1()
'
' BUTTON1 Macro
' Macro recorded 08/29/2005 by harrisonsl
'

'
Sheets("TURN-IN DOC").Select
Range("B6").Select
ActiveCell.FormulaR1C1 = "=BTR!R9C14"
Range("D10").Select
ActiveCell.FormulaR1C1 = "=BTR!R9C1"
Range("B12:E13").Select
ActiveCell.FormulaR1C1 = "=BTR!R9C26"
Range("B17").Select
ActiveCell.FormulaR1C1 = "=BTR!R9C51"
Range("B18").Select
End Sub

HERE'S WHAT I WANT TO HAPPEN. HOPEFULLY THIS ISN'T TOO CONFUSING. I
WROTE THIS SO YOU CAN REPLICATE THIS IN YOUR OWN EXCEL.:

BUTTON1 = MOVE ROW *9* SHEET2 (BTR) CELLS TO SHEET1 (TURN-IN DOC)
CELLS

SHEET2 CELLS
CELL: A*9*|N*9*|Z*9*|AY*9*
VALUE: 05W297|310|Flashlight,Mag,2Cell,withBaton|6230-01-432-6430

SHEET1 CELLS
CELL: D10|B6|B12|B17
CELL NAME: REPORT #|WORKCENTER|NOMENCLATURE|NSN

SHEET2 A*9* TO SHEET1 D10
SHEET2 N*9* TO SHEET1 B6
SHEET2 Z*9* TO SHEET1 B12
SHEET2 AY*9* TO SHEET1 B17

BUTTON2 = MOVE ROW *10* SHEET2 (BTR) CELLS TO SHEET 1 (TURN-IN DOC)
CELLS

SHEET2 CELLS
CELL: A*10*|N*10*|Z*10*|AY*10*
VALUE: 05W297|310|Flashlight,Mag,2Cell,withBaton|6230-01-432-6430

SHEET1 CELLS
CELL: D10|B6|B12|B17
CELL NAME: REPORT #|WORKCENTER|NOMENCLATURE|NSN

SHEET2 A*10* TO SHEET1 D10
SHEET2 N*10* TO SHEET1 B6
SHEET2 Z*10* TO SHEET1 B12
SHEET2 AY*10* TO SHEET1 B17

I need this for every row without recording each macro over and over
again.

Dave Peterson Wrote:
Post your code and what you want to happen.

--
Optitron

------------------------------------------------------------------------
Optitron's Profile:

http://www.excelforum.com/member.php...o&userid=26729
View this thread:

http://www.excelforum.com/showthread...hreadid=399813

--

Dave Peterson


--
Optitron
------------------------------------------------------------------------
Optitron's Profile: http://www.excelforum.com/member.php...o&userid=26729
View this thread: http://www.excelforum.com/showthread...hreadid=399813


--

Dave Peterson
  #17   Report Post  
Optitron
 
Posts: n/a
Default


Alright, it's good enough for government work. Thanks alot. :)


--
Optitron
------------------------------------------------------------------------
Optitron's Profile: http://www.excelforum.com/member.php...o&userid=26729
View this thread: http://www.excelforum.com/showthread...hreadid=399813

  #18   Report Post  
Optitron
 
Posts: n/a
Default


Now I need something else along the same lines. I need a button to move
2 cells in multiple selected rows to another sheet.

sheet2 -- sheet 1

row(rows selected) column a -- rows (# of rows selected) column a
row(rows selected) column z12 -- rows (# of rows selected) column b


--
Optitron
------------------------------------------------------------------------
Optitron's Profile: http://www.excelforum.com/member.php...o&userid=26729
View this thread: http://www.excelforum.com/showthread...hreadid=399813

  #19   Report Post  
Dave Peterson
 
Posts: n/a
Default

Maybe something like:

Option Explicit
Sub testme2()
Dim toWks As Worksheet
Dim actWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim iRow As Long
Dim DestCell As Range

Set actWks = ActiveSheet
Set toWks = Worksheets("Sheet1")

Set myRng = Intersect(Selection.EntireRow, actWks.Range("a:a"))

With toWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

With toWks
For Each myCell In myRng.Cells
iRow = myCell.Row
DestCell.Value = actWks.Cells(iRow, "a").Value
DestCell.Offset(0, 1).Value = actWks.Cells(iRow, "z").Value
Next myCell
End With
End Sub

I'm guessing that z12 meant column Z.

Optitron wrote:

Now I need something else along the same lines. I need a button to move
2 cells in multiple selected rows to another sheet.

sheet2 -- sheet 1

row(rows selected) column a -- rows (# of rows selected) column a
row(rows selected) column z12 -- rows (# of rows selected) column b

--
Optitron
------------------------------------------------------------------------
Optitron's Profile: http://www.excelforum.com/member.php...o&userid=26729
View this thread: http://www.excelforum.com/showthread...hreadid=399813


--

Dave Peterson
  #20   Report Post  
Optitron
 
Posts: n/a
Default


I copied what you wrote and renamed sheet2 to DRMO SHEET but nothing
happened. I took what you already showed me yesterday and I can get one
row to the sheet with this:

Option Explicit
Sub DRMO()
Dim prtWks As Worksheet
Dim actWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim iRow As Long

Set actWks = ActiveSheet
Set prtWks = Worksheets("DRMO SHEET")

Set myRng = Intersect(Selection.EntireRow, actWks.Range("a:a"))

With prtWks
For Each myCell In myRng.Cells
iRow = myCell.Row
.Range("a6").Value = actWks.Cells(iRow, "a").Value
.Range("e6").Value = actWks.Cells(iRow, "z").Value
Application.Calculate
Application.Goto .Range("a1"), scroll:=True
Next myCell
End With
End Sub


Dave Peterson Wrote:
Maybe something like:

Option Explicit
Sub testme2()
Dim toWks As Worksheet
Dim actWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim iRow As Long
Dim DestCell As Range

Set actWks = ActiveSheet
Set toWks = Worksheets("Sheet1")

Set myRng = Intersect(Selection.EntireRow, actWks.Range("a:a"))

With toWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

With toWks
For Each myCell In myRng.Cells
iRow = myCell.Row
DestCell.Value = actWks.Cells(iRow, "a").Value
DestCell.Offset(0, 1).Value = actWks.Cells(iRow, "z").Value
Next myCell
End With
End Sub

I'm guessing that z12 meant column Z.



--
Optitron
------------------------------------------------------------------------
Optitron's Profile: http://www.excelforum.com/member.php...o&userid=26729
View this thread: http://www.excelforum.com/showthread...hreadid=399813



  #21   Report Post  
Dave Peterson
 
Posts: n/a
Default

I had a mistake in today's code. I pasted to the bottom of sheet2 column A.
But then I didn't go to the next cell.

Option Explicit
Sub testme2()
Dim toWks As Worksheet
Dim actWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim iRow As Long
Dim DestCell As Range

Set actWks = ActiveSheet
Set toWks = Worksheets("Sheet1")

Set myRng = Intersect(Selection.EntireRow, actWks.Range("a:a"))

With toWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

With toWks
For Each myCell In myRng.Cells
iRow = myCell.Row
DestCell.Value = actWks.Cells(iRow, "a").Value
DestCell.Offset(0, 1).Value = actWks.Cells(iRow, "z").Value
'I added this to go down one row.
Set DestCell = Destcell.offset(1,0)
Next myCell
End With
End Sub

It does assume that the cell in column A of the input worksheet is not empty!



Optitron wrote:

I copied what you wrote and renamed sheet2 to DRMO SHEET but nothing
happened. I took what you already showed me yesterday and I can get one
row to the sheet with this:

Option Explicit
Sub DRMO()
Dim prtWks As Worksheet
Dim actWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim iRow As Long

Set actWks = ActiveSheet
Set prtWks = Worksheets("DRMO SHEET")

Set myRng = Intersect(Selection.EntireRow, actWks.Range("a:a"))

With prtWks
For Each myCell In myRng.Cells
iRow = myCell.Row
Range("a6").Value = actWks.Cells(iRow, "a").Value
Range("e6").Value = actWks.Cells(iRow, "z").Value
Application.Calculate
Application.Goto .Range("a1"), scroll:=True
Next myCell
End With
End Sub

Dave Peterson Wrote:
Maybe something like:

Option Explicit
Sub testme2()
Dim toWks As Worksheet
Dim actWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim iRow As Long
Dim DestCell As Range

Set actWks = ActiveSheet
Set toWks = Worksheets("Sheet1")

Set myRng = Intersect(Selection.EntireRow, actWks.Range("a:a"))

With toWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

With toWks
For Each myCell In myRng.Cells
iRow = myCell.Row
DestCell.Value = actWks.Cells(iRow, "a").Value
DestCell.Offset(0, 1).Value = actWks.Cells(iRow, "z").Value
Next myCell
End With
End Sub

I'm guessing that z12 meant column Z.


--
Optitron
------------------------------------------------------------------------
Optitron's Profile: http://www.excelforum.com/member.php...o&userid=26729
View this thread: http://www.excelforum.com/showthread...hreadid=399813


--

Dave Peterson
  #22   Report Post  
Optitron
 
Posts: n/a
Default


Ok it's working. It put the data on the next page down. It's all good
now. Thanks.

Dave Peterson Wrote:
I had a mistake in today's code. I pasted to the bottom of sheet2
column A.
But then I didn't go to the next cell.

Option Explicit
Sub testme2()
Dim toWks As Worksheet
Dim actWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim iRow As Long
Dim DestCell As Range

Set actWks = ActiveSheet
Set toWks = Worksheets("Sheet1")

Set myRng = Intersect(Selection.EntireRow, actWks.Range("a:a"))

With toWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

With toWks
For Each myCell In myRng.Cells
iRow = myCell.Row
DestCell.Value = actWks.Cells(iRow, "a").Value
DestCell.Offset(0, 1).Value = actWks.Cells(iRow, "z").Value
'I added this to go down one row.
Set DestCell = Destcell.offset(1,0)
Next myCell
End With
End Sub

It does assume that the cell in column A of the input worksheet is not
empty!



--
Optitron
------------------------------------------------------------------------
Optitron's Profile: http://www.excelforum.com/member.php...o&userid=26729
View this thread: http://www.excelforum.com/showthread...hreadid=399813

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
copy 45 rows down, repetativly, only on button click? swatsp0p Excel Worksheet Functions 1 May 25th 05 04:06 PM
copy exact values from RangeA to Range B which has extra rows guptasa@gossami .com Excel Discussion (Misc queries) 1 May 16th 05 09:21 AM
Copy Rows if Lookup Criteria Match vdoubleu Excel Discussion (Misc queries) 0 March 29th 05 06:43 PM
Copy rows of data to another worksheet where ReturnDate is blank Helen McClaine Excel Discussion (Misc queries) 2 March 28th 05 11:33 PM
How Can I copy a sheet that has hidden rows without copying the h. ibrahim Excel Worksheet Functions 1 January 13th 05 02:25 AM


All times are GMT +1. The time now is 06:58 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"