printing in excel 5

[smiley0q0]

is there any way to have 2 or more different sheets print to the same page in excel?

 

[Dreamboat]

Hi smiley. I think you can do that in VBA. There is no "easy way". You'll have to check with JVFriederick...or someone else.
techsupportgirl@home.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word

 

[JVFriederick]

Hey - I still use a copy machine for that type of thing. You mean there's another way?

 

[GeekGirlau]

The only way I know of to do this is via code - you can try the sample below. This copies all active cells from Sheet1 and Sheet2 to Sheet3, then previews the document. It's fairly basic, but with some modification it may work for you!


Dim cell, newSheet
Dim intColLast As Integer
Dim intColStart As Integer
Dim intRowLast As Integer
Dim i As Integer


Sheets("Sheet1&quot.Select
intColLast = ActiveCell.SpecialCells(xlLastCell).Column
intRowLast = ActiveCell.SpecialCells(xlLastCell).Row
intColStart = intColLast + 1

Set newSheet = Sheets("Sheet3&quot

For Each cell In Sheets("Sheet1&quot.Cells
If cell.Column <= intColLast And _
cell.Row <= intRowLast Then

If cell.Formula <> &quot;&quot; Then
newSheet.Range(cell.Address).Formula = cell.Value
End If
End If

If cell.Column > intColLast And _
cell.Row > intRowLast Then
Exit For
End If
Next

Sheets(&quot;Sheet2&quot.Select
intColLast = ActiveCell.SpecialCells(xlLastCell).Column
intRowLast = ActiveCell.SpecialCells(xlLastCell).Row

For Each cell In Sheets(&quot;Sheet2&quot.Cells
If cell.Column <= intColLast And _
cell.Row <= intRowLast Then
If cell.Formula <> &quot;&quot; Then
newSheet.Range(cell.Address).Offset(0, intColStart).Formula = cell.Value
End If
End If

If cell.Column > intColLast And _
cell.Row > intRowLast Then
Exit For
End If
Next

Sheets(&quot;Sheet3&quot.Select

With ActiveSheet.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperA4
.FitToPagesWide = 1
.FitToPagesTall = False
End With

ActiveWindow.SelectedSheets.PrintPreview

 

[smiley0q0]

WOW, this is really GREAT!! Thanks GeekGirl i will definatly be able to use this.

Appreciativly,
Smiley ... :^)

 

[DaleWatson123321]

Hi Smiley and GeekGirl,

I was anxious to try the code for merging the data.

Unfortunately, I tried 3 times and it failed every time. I guess it depends upon the individual arrangement of the data on each sheet.

May I suggest another alternative. If the only alternative is to merge the data, and the data to be included is &quot;pre-determined&quot;, what about first defining the data ranges with RANGE NAMES, and then simply referencing those range names.

I have learned that VERY FEW Excel users (at least novices) have been taught the extreme value of using Range Names. I liken their use to making the spreadsheet as &quot;user-friendly&quot; as a City becomes when it has houses which have been assigned &quot;Street Names and Numbers&quot;. WITHOUT using Ranges Names, the spreadsheet is like trying to navigate around a city which has NO Street Names and houses without addresses.

Thanks nonetheless to GeekGirl for your contribution.

BUT, I would like to believe that all of us (Excel users) can somehow &quot;ban together&quot; and create enough of a &quot;voice&quot; that Microsoft will &quot;finally&quot; get their act together on this issue of being UNABLE to print more than one range on ONE page.

This has been a VERY, VERY LONG-STANDING BUG (RESTRICTION) in Excel and NEEDS to be fixed. The ability to print multiple ranges on ONE page has been possible with Lotus 123 for SEVERAL YEARS - so WHY NOT with Excel ? ? ?

I have made this point in other responses, but it warrants repeating. I have contacted Microsoft regarding this issue and they have FAILED TO RESPOND with confirmation that they will fix this BUG.

If all of us Excel users can &quot;do our part&quot; and COMPLAIN to Microsoft, &quot;LONG and LOUD&quot;, I expect we will be able to &quot;make a difference&quot;.

To be blunt, it seems extremely ABSURD that Microsoft has not taken the time to solve this SIGNIFICANT BUG, because it should not be any more difficult than changing or eliminating ONE LINE OF CODE ...the one which &quot;forces a page-break&quot; when printing two or more disjointed ranges.

This &quot;forced page-break&quot; logically should NEVER have been included in the first place, simply because it has NO value.

It should logically be left up to the end-user to insert a &quot;forced page break&quot; if required, an option which has always been available in Excel.

This situation (bug/solution) is simply &quot;COMMON SENSE&quot;. So, why can't Microsoft &quot;GET THE MESSAGE&quot;, and eliminate this BUG. They seemingly are NOT &quot;tuning in&quot; to forums like Tek-Tips, nor listening to its own customer base.

WHY MICROSOFT ? ? ? ? ? ? ...please share your reasoning with those of us who have to struggle with &quot;workarounds&quot; like the one GeekGirl created.

&quot;Venting complete&quot;, for now. ...but I am still waiting for someone at Microsoft to &quot;reach down&quot; and communicate with us &quot;common folk&quot;, and of course to FIX THIS BUG.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[GeekGirlau]

Dale, I'm with you 100%. Honestly, the number of things Microsoft would have done differently if they'd asked us about it ... don't get me started!

In the meantime, I've made the code a little more robust until Bill Gates gets back to me.

The code combines the first two sheets (regardless of their name) into a third. It first checks the number of columns and will not proceed if there are more than 256. It also clears any existing print areas on the existing sheets.

I'm sure there are other refinements we could make here, so feel free to add your $0.02 worth!


Sub CombineSheets()
Dim cell, newSheet
Dim strLastCell As String
Dim intColLast(1 To 2) As Integer
Dim intColTotal As Integer
Dim strColStart As String
Dim i As Integer


On Error GoTo CombineErr

For i = 1 To 2
Sheets(i).Select
intColLast(i) = ActiveCell.SpecialCells(xlLastCell).Column
Next i

intColTotal = intColLast(1) + intColLast(2)

If intColTotal > 256 Then
MsgBox &quot;There are too many columns to combine these &quot; & _
&quot;sheets for printing!&quot;, vbExclamation, _
&quot;Cannot Combine Sheets&quot;
GoTo ExitCombine
End If

Sheets(&quot;Combined&quot.Select
ActiveWindow.SelectedSheets.Delete

CombineSheets:
For i = 1 To 2
Sheets(i).Select
ActiveSheet.PageSetup.PrintArea = &quot;&quot;

strLastCell = ActiveCell.SpecialCells(xlLastCell).Address

Range(&quot;A1:&quot; & strLastCell).Name = &quot;Combine&quot; & i

If i = 1 Then
strColStart = Cells(1, intColLast(i) + 1).Address
End If
Next i

Set newSheet = Sheets.Add
newSheet.Name = &quot;Combined&quot;

Range(&quot;Combine1&quot.Copy
Worksheets(&quot;Combined&quot.Range(&quot;A1&quot.PasteSpecial xlPasteValues

Range(&quot;Combine2&quot.Copy
Worksheets(&quot;Combined&quot.Range(strColStart).PasteSpecial xlPasteValues

Sheets(&quot;Combined&quot.Select

With ActiveSheet.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperA4
.FitToPagesWide = 1
.FitToPagesTall = False
End With

ActiveWindow.SelectedSheets.PrintPreview


ExitCombine:
Exit Sub


CombineErr:
Select Case Err.Number
Case 9
Resume CombineSheets

Case Else
MsgBox Err.Number & &quot;: &quot; & Err.Description
Resume ExitCombine
End Select
End Sub

 

[DaleWatson123321]

Hi GeekGirlau,

I finally found time to test out your revised routine. Thanks, it's an improvement. It works fine for two sheets.

I tried modifying it for 3 sheets, but wasn't able to get it to work correctly. No doubt it can be made to work - it's just a matter of one taking the time to make the proper adjustments. As I don't have an application right now where I need to have such a routine for merging multiple sheets/ranges, I won't pursue this for now.

I want to thank you, however, for your contribution and your support regarding my comments about the need for Microsoft to FIX THIS BUG/LIMITATION - specifically to ELIMINATE THE &quot;FORCED PAGE-BREAK&quot; - and simply allow the user to insert a page-break if necessary. Given that it has always been EXTREMELY EASY in Lotus 123 to print multiple ranges (simply by separating each range with a semi-colon), it defies explanation as to why Microsoft has refused to address this issue.

I hope that more and more Tek-Tips readers will &quot;SPEAK UP&quot;, &quot;STAND-UP&quot; ...do SOMETHING ...like perhaps an email to Microsoft and/or complaining through Tek-Tips about this LONGSTANDING BUG in Excel.

It really is ABSURD that Microsoft has IGNORED Microsoft Excel users FOR SO LONG - ESPECIALLY when this BUG is EXTREMELY E A S Y to fix !!!!!!!

I gave another STAR for your efforts GeekGirlau. I am also hoping to cause other Tek-Tips readers to take notice, and to &quot;help out in reaching Microsoft&quot;.

And, who knows, perhaps if we can all &quot;make enough waves&quot;, Microsoft will finally hear us and FIX THIS BUG.

They could start by first acknowledging this BUG through this Tek-Tips forum. Is this too much to expect from Microsoft ??? ...perhaps, but let's issue them a CHALLENGE.

MICROSOFT: Please SHOW YOUR CUSTOMERS THAT YOU CARE ...by (at the very least) responding to this posting, and preferably announce that you WILL fix this BUG A.S.A.P.

...Dale Watson dwatson@bsi.gov.mb.ca