If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
reminder notifications in a column | L Mieth | General Discussion | 6 | June 10th, 2005 11:00 AM |
Select alternate rows to copy | Christina | General Discussion | 4 | January 27th, 2005 01:05 AM |
I dont want to copy hidden rows | sh0t2bts | Worksheet Functions | 2 | June 23rd, 2004 12:29 AM |
macro to insert rows and copy and paste | STEVE | Worksheet Functions | 1 | June 4th, 2004 06:26 PM |
Copy and Paste hidden rows | DuncVern | Worksheet Functions | 1 | April 1st, 2004 05:16 PM |