Format Excel Worksheet

  Juni 23:33 30 Sep 2004
Locked

I need to print a worksheet (to make an order form) that contains 5 columns, but split into 2 so that one sheet looks like 2 sheets of A5 (A4 landscape, but wit two pages like a book - does this make any sense to anyone, !!!!!!!!!

  JonnyTub 23:34 30 Sep 2004

VoG's territory!!!

  VoG II 23:40 30 Sep 2004

Yes. File/Print Area and set your options there.

  Retired Hurt 23:41 30 Sep 2004

Do you mean there will be 2 forms, each with 5 columns, on an A4 sheet? If so why not construct 2 tables on a landscape Word page divided into 2 columns? Divide the page into 2 columns; Make the table on one side of the page; then copy and paste the second page when it is as you want it?

  Juni 08:02 01 Oct 2004

Thank you Retired Hurt for your response. No there is only one form but the second page would be a continuation. The information I am using for the form is currently in a excel file. I.E. with 700 items listed in one column. with separate columns for availability, price, VAT Total. The list runs in alphabetical order. The reason for this format, is if I print the form on one page, there is a lot of wasted space, and the order form currently runs into 20 pages, need to cut this down, as it is not cost effective for print.

  cherria 14:24 01 Oct 2004

If I understand you correctly, there is no way to do this in Excel short of taking your 700 lines and breaking it in 2 so that it is 2 sets of 350 lines next to each other each 5 columns wide with headers at the top of each one.

To do what you want without breaking the table of data, the best way would probably be to use Word and mail merge into a form document that you have set up to print the order form.

As Cherria has suggested you will need to modify the way your list is handled.

I suggest that you layout your form on a separate part of your worksheet or even a second worksheet.

Once you have the blank form looking exactly as you want it and printing correctly then it is a simple matter to populate the form with VBA.

The original list would remain as is. I assume that you would want say the first 30 entries on the left and the next 30 on the right.

HTH

I have assumed that all of your data is on the first sheet in your workbook 'Sheets(1)' in the accompanying code. The five headings are in Row 1 and all of your data is in consecutive rows from row 2. The code will continue until it reaches the end OR the first blank cell.

The next assumption is that when you have laid out your form as you wish that there is room for 35 rows to be printed, if more or less change the 'If jRow = 37' to the appropriate figure.

The final assumption is that the form is on the second sheet 'Sheets(2)' in the code and that you wish to have the first 35 rows of data in columns A to E and the next 35 rows in columns G to K with the same Headings in Row 1.

The code will give you a Print Preview of each sheet of the form, moving to the next one when you select Close or Esc, you can either Print each page from the Print Preview page or else substitute 'Sheets(2).PrintPreview' with 'Sheets(2).Printout'

Select all of the code that follows and place in a module in the VBE

'==========================================

Option Explicit

Sub PrintForms()
Dim iRow As Integer, jRow As Integer

Dim iCol As Integer, kCol As Byte

Dim iStart As Byte, iFinish As Byte

Dim SecondSet As Boolean

iRow = 2

jRow = 2

SecondSet = False

iStart = 1

iFinish = 5

Do Until Sheets(1).Cells(iRow, 1) = ""

Application.ScreenUpdating = False

kCol = 1

For iCol = iStart To iFinish

Sheets(2).Cells(jRow, iCol) = Sheets(1).Cells(iRow, kCol)

kCol = kCol + 1

Next iCol

jRow = jRow + 1

iRow = iRow + 1

If jRow = 37 Then

SecondSet = Not SecondSet

If SecondSet Then

iStart = 7

iFinish = 11

Else

Application.ScreenUpdating = True

Sheets(2).PrintPreview

Application.ScreenUpdating = False

Call ClearForm

iStart = 1

iFinish = 5

End If

jRow = 2

End If

Loop

If Sheets(2).Cells(2, 1) <> "" Then

Sheets(2).PrintPreview

End If

End Sub

'==========================================

Sub ClearForm()

Sheets(2).Activate

Range("A2:K36").Select

Selection.ClearContents

End Sub

'==========================================

  Juni 20:34 08 Oct 2004

Sincere thanks whisperer for your time and effort in trying to resolve my problem. As a relatively new user to excel. Your advice was a bit too technical due to my lack of knowledge (I'm thick as well!) but thank you anyway!

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

Alice Saey's mesmerising animation for Dutch singer Mark Lotterman

iPad Pro 10.5in (2017) review

Comment booster votre iPhone ?