function question??

M

Mekinnik

Can a function call another function? Here is the function I have. I am tring
to apply a page break (which works fine) then tring to call my format page
funtion after it applies the page break??

Sub setPage()
Dim iRow As Long
Dim myCell As Range
Dim myRng As Range
Dim wks As Worksheet
Dim searchFor As String

searchFor = FrmCreate.CbxDept.Text
Set wks = Worksheets(searchFor)
With wks
.ResetAllPageBreaks
Set myRng = Nothing
On Error Resume Next
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No visible cells!"
Exit Sub
End If

iRow = 0
For Each myCell In myRng.Cells
iRow = iRow + 1
If iRow > 1 Then
If iRow Mod 19 = 1 Then
.HPageBreaks.Add before:=myCell
Call FormatHeaders
End If
End If
Next myCell
End With

End Sub
 
F

FSt1

hi
yes. use the call command.
but you already have a call command in your code.

call FormatHeaders

???
regards
FSt1
 
M

Mekinnik

Yes I know, but it does not seem to work the way I want it to. maybe that the
problem? I guess another post for that issue, unless you want to try to help
within this post?
 
M

Mekinnik

What I have is a sheet that is created a populated with data and then the
created sheet is formated through code, and I want to insert a page break at
the end of the data, then apply the format page function again to create a
new page and apply the rest of the data so on and so forth. Here is the code
that starts it.

Private Sub BtnGo_Click()
Dim rgMatch As Range '''' range of matches
Dim searchFor As String ''' string to search for
Dim wsh As Worksheet ''' where to search
Dim rgToSearch As Range ''' where to search
Dim RgFrom As Range
Dim n As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'copies all data that matches 'searchFor' to new sheet
searchFor = Me.CbxDept.Text
Set wsh = Sheets("Procode")
Set rgToSearch = wsh.Range("M:M")
Set RgFrom = wsh.Range("A1:M1").EntireColumn
n = Int(56 * Rnd + 1)

''' Search all matches
Set rgMatch = FindAll(rgToSearch, searchFor & "*", xlValues, xlWhole)

''' Process matches
If Not rgMatch Is Nothing Then
''' copy specific columns to new sheet

On Error Resume Next
Application.DisplayAlerts = False
wsh.Parent.Worksheets(searchFor).Delete
Application.DisplayAlerts = True
On Error GoTo 0
With wsh.Parent.Worksheets.Add
''' copy second column: B->B
Application.Intersect(rgMatch.EntireRow, wsh.Range("B:B")).Copy
..Range("B5")
''' copy third column : C->H
Application.Intersect(rgMatch.EntireRow, wsh.Range("C:C")).Copy
..Range("H5")
''' copy forth column : D->I
Application.Intersect(rgMatch.EntireRow, wsh.Range("D:D")).Copy
..Range("I5")
''' copy fifth column: E->J
Application.Intersect(rgMatch.EntireRow, wsh.Range("E:E")).Copy
..Range("J5")
''' copy sixth column: F->K
Application.Intersect(rgMatch.EntireRow, wsh.Range("F:F")).Copy
..Range("K5")
''' copy seventh column : G->L
Application.Intersect(rgMatch.EntireRow, wsh.Range("G:G")).Copy
..Range("L5")
''' copy eighth column: H->M
Application.Intersect(rgMatch.EntireRow, wsh.Range("H:H")).Copy
..Range("M5")
''' copy ninth column: I->N
Application.Intersect(rgMatch.EntireRow, wsh.Range("I:I")).Copy
..Range("N5")
''' copy tenth column : J->O
Application.Intersect(rgMatch.EntireRow, wsh.Range("J:J")).Copy
..Range("O5")
''' copy eleventh column: K->P
Application.Intersect(rgMatch.EntireRow, wsh.Range("K:K")).Copy
..Range("P5")
''' copy twelveth column: L->Q
Application.Intersect(rgMatch.EntireRow, wsh.Range("L:L")).Copy
..Range("Q5")
''' copy last column: M->A
Application.Intersect(rgMatch.EntireRow, wsh.Range("M:M")).Copy
..Range("A5")
Call FormatHeaders
'''change the tab color randomly and rename sheet
.Tab.ColorIndex = n
.Name = searchFor
Call setPage
'Call InsertBreak
End With

End If
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub


Public Function FindAll(where As Range, what As Variant, lookIn As
XlFindLookIn, lookAt As XlLookAt) As Range
Dim rgResult As Range
Dim cell As Range
Dim firstAddr As String

With where
Set cell = .Find(what, lookIn:=lookIn, lookAt:=lookAt)
If Not cell Is Nothing Then
firstAddr = cell.Address
Do
''' add cell to result range
If rgResult Is Nothing Then
Set rgResult = cell
Else
Set rgResult = Application.Union(rgResult, cell)
End If
''' find next match
Set cell = .FindNext(cell)
Loop While Not cell Is Nothing And cell.Address <> firstAddr
End If
End With

Set FindAll = rgResult
End Function
 
M

Mekinnik

Here are the function:

Public Function FormatHeaders()
'format row and columns
Rows("1:1").RowHeight = 45
Rows("2:2").RowHeight = 15.75
Rows("3:3").RowHeight = 21.75
Rows("4:4").RowHeight = 13
Rows("5:21").RowHeight = 33
Columns("A:A").ColumnWidth = 6.57
Columns("E:F").ColumnWidth = 8.43
Columns("G:G").ColumnWidth = 15
Columns("H:H").ColumnWidth = 2.96
Columns("L:M").ColumnWidth = 6.14
Columns("N:N").ColumnWidth = 6.29
Range("C:C,O:O,P:p").ColumnWidth = 6.86
Columns("Q:Q").ColumnWidth = 8.71
Range("B:B,D:D,I:K").ColumnWidth = 6

'formats row 1 for heading
With Range("A1:Q1")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Merge
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Font.Name = "Arial"
.Font.Size = 36
.Font.Bold = True
Range("A1").FormulaR1C1 = "Hazardous Material Inventory"
End With
'formats row 2
With Range("A2")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Font.Name = "Arial"
.Font.Size = 12
.Font.Bold = True
Range("A2").FormulaR1C1 = "Unit:"
End With
With Range("B2:E2")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Merge
.Font.Name = "Arial"
.Font.Size = 12
End With
With Range("A2:E2")
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
End With
With Range("F2:G2")
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Font.Name = "Arial"
.Font.Size = 12
.Font.Bold = True
Range("F2").FormulaR1C1 = "Department/Division:"
End With
With Range("H2:L2")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Merge
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
End With
With Range("M2")
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.Font.Name = "Arial"
.Font.Size = 12
.Font.Bold = True
Range("M2").FormulaR1C1 = "Date:"
End With
With Range("N2:Q2")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Merge
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
End With
'format coulmn 3
With Range("A3:A4")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Merge
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Font.Name = "Arial"
.Font.Size = 9
.Font.Bold = True
Range("A3").FormulaR1C1 = "MSDS#"
End With
With Range("B3:E4")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Merge
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Font.Name = "Arial"
.Font.Size = 9
.Font.Bold = True
Range("B3").FormulaR1C1 = "Product Name"
End With
With Range("F3:G4")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Merge
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.WrapText = True
.Font.Name = "Arial"
.Font.Size = 8
.Font.Bold = True
Range("F3").FormulaR1C1 = "Manufacturers Name Phone Number"
End With
With Range("H3:H4")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.Merge
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.WrapText = True
.Font.Name = "Arial"
.Font.Size = 8
.Font.Bold = True
Range("H3").FormulaR1C1 = "A / I"
End With
With Range("I3:I4")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.Merge
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.WrapText = True
.Font.Name = "Arial"
.Font.Size = 8
.Font.Bold = True
Range("I3").FormulaR1C1 = "EHS (302) YES/NO"
End With
With Range("J3:J4")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.Merge
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.WrapText = True
.Font.Name = "Arial"
.Font.Size = 8
.Font.Bold = True
Range("J3").FormulaR1C1 = "Toxic (313) YES/NO"
End With
With Range("K3:N3")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Merge
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Font.Name = "Arial"
.Font.Size = 9
.Font.Bold = True
Range("K3").FormulaR1C1 = "NFPA/HMIS Rating"
End With
With Range("K4")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Font.Name = "Arial"
.Font.Size = 8
.Font.Bold = True
Range("K4").FormulaR1C1 = "Fire"
End With
With Range("L4")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Font.Name = "Arial"
.Font.Size = 8
.Font.Bold = True
Range("L4").FormulaR1C1 = "Health"
End With
With Range("M4")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Font.Name = "Arial"
.Font.Size = 8
.Font.Bold = True
Range("M4").FormulaR1C1 = "React"
End With
With Range("N4")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Font.Name = "Arial"
.Font.Size = 8
.Font.Bold = True
Range("N4").FormulaR1C1 = "Specific"
End With
With Range("O3:O4")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.Merge
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.WrapText = True
.Font.Name = "Arial"
.Font.Size = 8
.Font.Bold = True
Range("O3").FormulaR1C1 = "Disposal Code R/Y/G"
End With
With Range("P3:p4")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.Merge
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.WrapText = True
.Font.Name = "Arial"
.Font.Size = 8
.Font.Bold = True
Range("P3").FormulaR1C1 = "Quantity on Hand"
End With
With Range("Q3:Q4")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Merge
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.WrapText = True
.Font.Name = "Arial"
.Font.Size = 8
.Font.Bold = True
Range("Q3").FormulaR1C1 = "Date of Inventory"
End With
With Range("A5:A21")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
End With
With
Range("B5:E5,B6:E6,B7:E7,B8:E8,B9:E9,B10:E10,B11:E11,B12:E12,B13:E13,B14:E14,B15:E15,B16:E16,B17:E17,B18:E18,B19:E19,B20:E20,B21:E21")
.Font.Name = "Arial"
.Font.Size = 9
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Merge
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
End With
With
Range("F5:G5,F6:G6,F7:G7,F8:G8,F9:G9,F10:G10,F11:G11,F12:G12,F13:G13,F14:G14,F15:G15,F16:G16,F17:G17,F18:G18,F19:G19,F20:G20,F21:G21")
.Font.Name = "Arial"
.Font.Size = 8
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.Merge
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
End With
With Range("H5:H21")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
End With
With Range("I5:J21")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.Borders(xlInsideVertical).LineStyle = xlContinuous
.NumberFormat = """Yes"";""Yes"";""No"""
End With
With Range("K5:M21")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.Borders(xlInsideVertical).LineStyle = xlContinuous
.NumberFormat = "0"
End With
With Range("N5:O21")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.Borders(xlInsideVertical).LineStyle = xlContinuous
.NumberFormat = "0"
End With
With Range("P5:p21")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.NumberFormat = "0"
End With
With Range("Q5:Q21")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.NumberFormat = "[$-409]d-mmm-yy;@"
End With
End Function
Sub setPage()
Dim iRow As Long
Dim myCell As Range
Dim myRng As Range
Dim wks As Worksheet
Dim searchFor As String

searchFor = FrmCreate.CbxDept.Text
Set wks = Worksheets(searchFor)
With wks
.ResetAllPageBreaks
Set myRng = Nothing
On Error Resume Next
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No visible cells!"
Exit Sub
End If

iRow = 0
For Each myCell In myRng.Cells
iRow = iRow + 1
If iRow > 1 Then
If iRow Mod 19 = 1 Then
.HPageBreaks.Add before:=myCell
Call FormatHeaders
End If
End If
Next myCell
End With

End Sub
 
F

FSt1

hi
change...
public function formatheaders()
to..
sub formatheaders()
and change end function at the bottom to end sub.
it should work now.

subs are code that do things. functions create custom formulas and such and
are not ment to perform direct actions like subs.

Regards
FSt1
 
M

Mekinnik

Thank you for pointing that out. The problem I am having is with the
formatHeaders sub, and it works for the first time, however it will not
format after the page break because of the fact that I have it formatting
particular cells and rows and I need the sub to determine how many rows of
data and then format however many pages it needs. I could if you would like
send you what I have and maybe it would help to understand what it that I am
trying to accomplish.
 
B

Bob Phillips

Maybe you should pass the worksheet to be worked upon as parameters to the
called functions, and qualify the ranges with the worksheet object.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
F

FSt1

hi
i just ran your code. slick. chemicals. i use to work with that stuff.
anyway your formating only goes down to row 21 which is your problem.
so to find the last row of your data add this to your code somewhere near
the top.

Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row

then go through your code and replace lines line this.....

With Range("Q5:Q21")

with this..

With Range("Q5:Q" & lr)

that should format to the bottom of the data.

good luck
FSt1
 
M

Mekinnik

Sorry Bob,
That is just a tad bit beyond me, I am learning as I go and I do not quite
understand how to do what you suggest and am not sure then end result would
be what I have visioned.
 
M

Mekinnik

Tried what you suggested, however that is not what I am trying to do. I need
to make the formatHeaders sub fire starting in the row after so many rows, in
this case due to page constraints it has to fire after every page break which
is set to the top of every 19th row. I think it does or hope it is set
right. Any suggestions? I believe I posted my setupPage sub in a previous
reply
 
B

Bob Phillips

Well, for instance,

Public Function FormatHeaders(ByRef ws As Worksheet)
'format row and columns
ws.Rows("1:1").RowHeight = 45
ws.Rows("2:2").RowHeight = 15.75
ws.Rows("3:3").RowHeight = 21.75
ws.Rows("4:4").RowHeight = 13
ws.Rows("5:21").RowHeight = 33
ws.Columns("A:A").ColumnWidth = 6.57
ws.Columns("E:F").ColumnWidth = 8.43
ws.Columns("G:G").ColumnWidth = 15
ws.Columns("H:H").ColumnWidth = 2.96
ws.Columns("L:M").ColumnWidth = 6.14

etc., an when calling it use

Call FormatHeaders(ws:=Worksheets("the name of the worksheet to be
formatted")

and so on with the other functions.


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Remove Identical words 0
Page break issues 1
Runtime Error 1004:Method 'Range' of object'_Worksheet' Failed 4
Match style 0
Subtotal multiple columns and ... 0
Insert rows by group and copy value 2
Insert Row based on count and copy range to inserted rows 2
Macro adds "=" to each cell in defined range across multiple worksheets 5

Top