A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Copy buttons by rows



 
 
Thread Tools Display Modes
  #1  
Old August 28th, 2005, 11:05 AM
Optitron
external usenet poster
 
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  
Old August 28th, 2005, 02:01 PM
Dave Peterson
external usenet poster
 
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  
Old August 28th, 2005, 02:46 PM
Optitron
external usenet poster
 
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  
Old August 28th, 2005, 03:31 PM
bigwheel
external usenet poster
 
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  
Old August 28th, 2005, 04:41 PM
Optitron
external usenet poster
 
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  
Old August 28th, 2005, 05:30 PM
bigwheel
external usenet poster
 
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  
Old August 28th, 2005, 06:44 PM
Dave Peterson
external usenet poster
 
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  
Old August 28th, 2005, 06:52 PM
Optitron
external usenet poster
 
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  
Old August 28th, 2005, 08:08 PM
bigwheel
external usenet poster
 
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  
Old August 28th, 2005, 08:43 PM
Optitron
external usenet poster
 
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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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 02: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


All times are GMT +1. The time now is 10:32 AM.


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