Excel Printout

  • Thread starter Thread starter becky57
  • Start date Start date
B

becky57

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.
 
Copy the sheet for printing only.

Select column C and paste special>values.

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
 
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.
 
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-click>Insert>Module.

Paste the macro into that module.

Alt + q to return to Excel window.

With the copy of the sheet active, run the macro from Tools>Macro>Macros.


Gord
 
Back
Top