Title Header Using Cell Value & Font Size

[nehpets12]

Help Setting The Header Please

I am trying to use the value in column H as the Header If you run this code the first supplier is ok but the second one prints out very large text size.


On this Line of code .CenterHeader = "&""Arial,Bold""Progress Report Printed On " & "&D" & Chr(10) & "&12" & HeaderSupplier

It is setting the text size to Arial Bold 12 but when it reaches the supplier "3 WAY ENGINEERING (2002) LIMITED" it is joining up the font size 12 and also the 3 of the supplier title to give me a text size of 123

I Think it is because I have not set a Dim Statement to the Supplier correctly.

Any help would be appreciated





Sub test()
Dim Supplier As String
Dim LastRow As Long
Dim RowFirst As Long
Dim MyRange As Range
Dim steve As String
Dim HeaderSupplier As Variant
'---------------------------

Supplier = ActiveSheet.Range("D2").Value
LastRow = ActiveSheet.Range("D65536").End(xlUp).Row
RowFirst = 2
rw = 2
While rw<= LastRow
While ActiveSheet.Cells(rw, 4).Value = Supplier _
And rw<= LastRow
rw = rw + 1
Wend
Set MyRange = ActiveSheet.Range(Cells(RowFirst, 1), Cells(rw - 1, 9))
HeaderSupplier = Cells(rw - 1, 8).Value
steve = ActiveSheet.Range(Cells(RowFirst, 1), Cells(rw - 1, 9)).Address
ActiveSheet.PageSetup.PrintArea = steve
With ActiveSheet.PageSetup
.CenterHeader = "&""Arial,Bold""Progress Report Printed On " & "&D" & Chr(10) & "&12" & HeaderSupplier
.Orientation = xlLandscape
.PrintGridlines = True
.LeftMargin = Application.InchesToPoints(0.196850393700787)
.RightMargin = Application.InchesToPoints(0.196850393700787)
.TopMargin = Application.InchesToPoints(0.92)
.BottomMargin = Application.InchesToPoints(0.99)
.HeaderMargin = Application.InchesToPoints(0.17)
.FooterMargin = Application.InchesToPoints(0.45)
.FitToPagesWide = 1
.FitToPagesTall = False
.Zoom = 97
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Supplier = ActiveSheet.Cells(rw, 4).Value
RowFirst = rw


Wend
End Sub

Book4
	A	B	C	D	E	F	G	H
1	PartNo	Description	OrderNo	Supplier	Ordered	Outstanding	DateDue	Supplier
2	028228	TEALHANDBASINELECTRIC24VOLT	077685	T3	2	2	06/02/2004	TEALPATENTSLTD.,
3	011640	PINHINGE	076155	T35	30	30	06/02/2004	3WAYENGINEERING(2002)LIMITED
Sheet1

 

[Monkey]

I don't know if it will work and if it works your proberbly your first caracter is a "space" but try this:

.CenterHeader = "&""Arial,Bold""Progress Report Printed On " & "&D" & Chr(10) & "&12 " & HeaderSupplier

or

.CenterHeader = "&""Arial,Bold""Progress Report Printed On " & "&D" & Chr(10) & "&12" & " " & HeaderSupplier

Greatings,

Monkey

 

[nehpets12]

Thankyou Works A treat