Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy 45 rows down, repetativly, only on button click? | Excel Worksheet Functions | |||
copy exact values from RangeA to Range B which has extra rows | Excel Discussion (Misc queries) | |||
Copy Rows if Lookup Criteria Match | Excel Discussion (Misc queries) | |||
Copy rows of data to another worksheet where ReturnDate is blank | Excel Discussion (Misc queries) | |||
How Can I copy a sheet that has hidden rows without copying the h. | Excel Worksheet Functions |