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

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.

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.

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.