Excel - How to Include Multiple columns on every page

[cosmogramma]

Hello

I'm generating some excel spreadsheets using VBA to specify page setup. Is there a way that I can include columns A, B, H and I on every page.

Example:
Page 1 [Columns A-N]
Page 2 [A,B,H,I,O-X]
Page 3 [A,B,H,I,Y-AI]
Page 4 [A,B,H,I, etc]

I'm really hoping someone can point me in the right direction. I need to generate these tomorrow.

Thanks


Local Time: 01:27 PM
Local Date: 05-06-2010

 

[SkipVought]

hi,

Move the data in H & I to C & D.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cosmogramma]

...what if I'm unable to move the C&D data to H&I?

Is this just impossible to do?

 

[SkipVought]

Use MS Query to order as desired, if you cannot disturb the source.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

This is BTW, a very strange requirement.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

This is BTW, a very strange requirement. I do not see that this can be done on second look.

Repeated Print Headings, be they Row or Column, must be CONTIGUOUS.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Try this.

Print Page 1.

Hide columns C-G

Print 2-end

Combine.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

...and I should have added, to CHANGE the page setup columns to print on each page, as required before printing.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cosmogramma]

Agreed. A ridiculous requirement even.

The users need this to be in Excel, as a regular spreadsheet they can edit. There are a larege number of columns so there are several pages across, and many more down. The first page needs to display all of the information, but the remaining pages only need to display Name, Company and Contact information (stored in A,B,H,I).

Is there any way to have columns A-I appear on all pages, but hide columns C-G on every page after the first?

I'm using: newSht.PageSetup.PrintTitleColumns = "$A:$I"

 

[SkipVought]

Print page 1

Hide the columns

Print 2 and following

Record as macro

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

Along the lines of Skip's suggestions,
Create a macro with a prominent "PRINT THIS FILE" button that formats the pages to print how it needs to, and then unhides stuff.

 

[cosmogramma]

Ok. I'll have to investigate the Print using a button option.

I appreciate the input. Thanks.

 

[cosmogramma]

Related question: I have this macro below that prints the first few pages. How do I automate the transfer of this macro (and an associated button) into the new spreadsheets I'm generating and sending out to people?

Sub RecordingPrintingAgain()
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _
:=True
Columns("C:G").Select
Selection.EntireColumn.Hidden = True
ActiveWindow.SelectedSheets.PrintOut From:=2, To:=5, Copies:=1, Collate _
:=True
End Sub

 

[SkipVought]

[b]
In the Workbook Obkect Code Window...[/b]
Private Sub Workbook_BeforePrint(Cancel As Boolean)
    RecordingPrintingAgain
End Sub
[b]
In your Project Module...[/b]
Sub RecordingPrintingAgain()
    ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _
        :=True
    Columns("C:G").EntireColumn.Hidden = True
    ActiveWindow.SelectedSheets.PrintOut From:=2, To:=5, Copies:=1, Collate _
        :=True
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cosmogramma]

I'm not sure I understand this solution.

My template Excel file will generate 20 or 30 spreadsheets (all separate Excel files), which will then be distributed to people. These newly generated files do not contain any of the macros the original Template file.

How can I ensure that in my newly generated spreadsheets, the Print Formatting macro is also transfered?

 

[SkipVought]

I was thinking about this last night and realized a flaw in my logic.

1. user hits PRINT

2. the Workbook_BeforePrint event does the following
checks a global variable to see if page 1 is printed.
If NOT then
formats and prints first page
stores a value in the global variable indicating that the first page is printed
else
formats and prints send thru last
stores a value in the global variable indicating that print is complete


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

in my newly generated spreadsheets, the Print Formatting macro is also transfered
Why not simply generate those spreadsheets from an .xlt having already the necessary code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gavona]

Never really fully explored them but I think you could also use Custom Views. Print1 with no columns hidden and Page1 set as print area. Print2toX with columns hidden and the relevant print area defined. ViewAll with things set up for onscreen use.
Users "could" be trained to use the views but I would stick to applying with VBA.
A variation on the theme, if users hide columns etc in utilising the workbook would be as step 1 to store the current settings as a custom view then re-apply them after printing.



Gavin

 

[cosmogramma]

Wow. Can't tell you how helpful these various suggestions have been. What I'm down to now, is a separate PrintFormatting macro that is transfered to each of the Workbooks I'm generating.

This macro Prints the spreadsheet accross and down as follows:
-Prints page 1
-Hides columns
-Prints pages 2,3,4
-Exposes columns
-Prints page 5
-Hides columns
-Prints pages 6,7,8
etc

This is currently done incorrectly using a For... Next loop until the max number of rows with data is reached. This is incorrect because obviously more than one row appears on a page.

How can I set this formatted printing up in a loop so that it stops when there is no more data?

 

[SkipVought]

Why are you constantly hiding columns???

I thought you had TWO scenarios.

1-print 1 page with columns A-H

2- print remaining pages with C-G hidden

Using this method, you probably need a HARD PAGE BREAK to determine page 1.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}