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
|
|||
|
|||
Excel Printout
I have a simple spreadsheet with 3 columns. 1-Emp #; 2-amounts; 3-sum of
column 2. Each column width is only 15.25. This spread sheet, if I should print it, would generate 24 pages. What I want to do is to reduce the number of pages by making 9 columns across. Do I have to cut and paste (because column 3 is a sum) or is there a way to take 3 pages and put to one sheet. Thank you. -- Becky57 |
#2
|
|||
|
|||
Excel Printout
Copy the sheet for printing only.
Select column C and paste specialvalues. Then run this macro on the copy to move sets of cells with a blank row inserted every 50 rows. Sub Move_Sets() Dim iSource As Long Dim iTarget As Long iSource = 1 iTarget = 1 Do Cells(iSource, "A").Resize(50, 3).Cut _ Destination:=Cells(iTarget, "A") Cells(iSource + 50, "A").Resize(50, 3).Cut _ Destination:=Cells(iTarget, "D") Cells(iSource + 100, "A").Resize(50, 3).Cut _ Destination:=Cells(iTarget, "G") iSource = iSource + 150 iTarget = iTarget + 51 Loop Until IsEmpty(Cells(iSource, "A").Value) End Sub After printing, delete the copy. Gord Dibben MS Excel MVP On Mon, 2 Feb 2009 11:25:03 -0800, becky57 wrote: I have a simple spreadsheet with 3 columns. 1-Emp #; 2-amounts; 3-sum of column 2. Each column width is only 15.25. This spread sheet, if I should print it, would generate 24 pages. What I want to do is to reduce the number of pages by making 9 columns across. Do I have to cut and paste (because column 3 is a sum) or is there a way to take 3 pages and put to one sheet. Thank you. |
#3
|
|||
|
|||
Excel Printout
Do I type this Macro exactly as you have typed it? I don't mean to sound
stupid...but I click on Record Macro and then type word for word, space for space; or do I have to enter any cell info between the parenthesis? Thanks for taking the time to answer my posting. -- Becky57 "Gord Dibben" wrote: Copy the sheet for printing only. Select column C and paste specialvalues. Then run this macro on the copy to move sets of cells with a blank row inserted every 50 rows. Sub Move_Sets() Dim iSource As Long Dim iTarget As Long iSource = 1 iTarget = 1 Do Cells(iSource, "A").Resize(50, 3).Cut _ Destination:=Cells(iTarget, "A") Cells(iSource + 50, "A").Resize(50, 3).Cut _ Destination:=Cells(iTarget, "D") Cells(iSource + 100, "A").Resize(50, 3).Cut _ Destination:=Cells(iTarget, "G") iSource = iSource + 150 iTarget = iTarget + 51 Loop Until IsEmpty(Cells(iSource, "A").Value) End Sub After printing, delete the copy. Gord Dibben MS Excel MVP On Mon, 2 Feb 2009 11:25:03 -0800, becky57 wrote: I have a simple spreadsheet with 3 columns. 1-Emp #; 2-amounts; 3-sum of column 2. Each column width is only 15.25. This spread sheet, if I should print it, would generate 24 pages. What I want to do is to reduce the number of pages by making 9 columns across. Do I have to cut and paste (because column 3 is a sum) or is there a way to take 3 pages and put to one sheet. Thank you. |
#4
|
|||
|
|||
Excel Printout
The macro recorder is not used for this type of operation.
First thing you do is make a backup of your workbook as it currently is configured. Then....................................... Just copy the macro from my original post then paste into a general module. To do that............................... With your workbook open and after you have copied the sheet and pasted special the values in column C. Alt + F11 to open the Visual Basic Editor. CTRL + r to open Project Explorer. Select your workbook/project and right-clickInsertModule. Paste the macro into that module. Alt + q to return to Excel window. With the copy of the sheet active, run the macro from ToolsMacroMacros. Gord On Tue, 3 Feb 2009 09:36:01 -0800, becky57 wrote: Do I type this Macro exactly as you have typed it? I don't mean to sound stupid...but I click on Record Macro and then type word for word, space for space; or do I have to enter any cell info between the parenthesis? Thanks for taking the time to answer my posting. |
Thread Tools | |
Display Modes | |
|
|