ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Page Break???????? (https://www.excelbanter.com/excel-programming/393245-page-break.html)

Kiba
Page Break????????
 
So I need to set a vertical page break and this is the macro I'm running but
it errors at this line.

Set ActiveSheet.VPageBreaks(1).Location = Range("S1")

Here's the full macro
__________________________________________________ _____________
Sub Test()
Set ActiveSheet.VPageBreaks(1).Location = Range("S1")
Columns("S:S").EntireColumn.AutoFit
ActiveWindow.ScrollColumn = 6
Columns("T:T").EntireColumn.AutoFit
Columns("U:U").EntireColumn.AutoFit
Columns("V:V").EntireColumn.AutoFit
Columns("X:X").Select
Columns("W:W").EntireColumn.AutoFit
Columns("X:X").EntireColumn.AutoFit
Range("T3").Select
ActiveCell.FormulaR1C1 = "=SUM(K:K)"
Range("T3:X3").Select
Selection.FillRight
Range("K1:O2").Select
Selection.Copy
Range("T1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("S3").Select
ActiveCell.FormulaR1C1 = "Tarkett Totals"
With ActiveCell.Characters(Start:=1, Length:=14).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("S1:X3").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub

JLGWhiz
Page Break????????
 
Two things. I'm not sure you need the Set command and you might need to
specify the sheet for Range("S1").

"Kiba" wrote:

So I need to set a vertical page break and this is the macro I'm running but
it errors at this line.

Set ActiveSheet.VPageBreaks(1).Location = Range("S1")

Here's the full macro
__________________________________________________ _____________
Sub Test()
Set ActiveSheet.VPageBreaks(1).Location = Range("S1")
Columns("S:S").EntireColumn.AutoFit
ActiveWindow.ScrollColumn = 6
Columns("T:T").EntireColumn.AutoFit
Columns("U:U").EntireColumn.AutoFit
Columns("V:V").EntireColumn.AutoFit
Columns("X:X").Select
Columns("W:W").EntireColumn.AutoFit
Columns("X:X").EntireColumn.AutoFit
Range("T3").Select
ActiveCell.FormulaR1C1 = "=SUM(K:K)"
Range("T3:X3").Select
Selection.FillRight
Range("K1:O2").Select
Selection.Copy
Range("T1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("S3").Select
ActiveCell.FormulaR1C1 = "Tarkett Totals"
With ActiveCell.Characters(Start:=1, Length:=14).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("S1:X3").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub

Jim Cone
Page Break????????
 

Try...
ActiveSheet.Columns("S").PageBreak = xlPageBreakManual
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins: sorting, comparing, matching, listing, finding...)



"Kiba"
wrote in message
So I need to set a vertical page break and this is the macro I'm running but
it errors at this line.

Set ActiveSheet.VPageBreaks(1).Location = Range("S1")

Here's the full macro
__________________________________________________ _____________
Sub Test()
Set ActiveSheet.VPageBreaks(1).Location = Range("S1")
-snip-
End Sub

Kiba
Page Break????????
 
I tried removing the set command and i tried naming the sheet but it didn't
help it still stopped at that line.


"JLGWhiz" wrote:

Two things. I'm not sure you need the Set command and you might need to
specify the sheet for Range("S1").

"Kiba" wrote:

So I need to set a vertical page break and this is the macro I'm running but
it errors at this line.

Set ActiveSheet.VPageBreaks(1).Location = Range("S1")

Here's the full macro
__________________________________________________ _____________
Sub Test()
Set ActiveSheet.VPageBreaks(1).Location = Range("S1")
Columns("S:S").EntireColumn.AutoFit
ActiveWindow.ScrollColumn = 6
Columns("T:T").EntireColumn.AutoFit
Columns("U:U").EntireColumn.AutoFit
Columns("V:V").EntireColumn.AutoFit
Columns("X:X").Select
Columns("W:W").EntireColumn.AutoFit
Columns("X:X").EntireColumn.AutoFit
Range("T3").Select
ActiveCell.FormulaR1C1 = "=SUM(K:K)"
Range("T3:X3").Select
Selection.FillRight
Range("K1:O2").Select
Selection.Copy
Range("T1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("S3").Select
ActiveCell.FormulaR1C1 = "Tarkett Totals"
With ActiveCell.Characters(Start:=1, Length:=14).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("S1:X3").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub

Kiba
Page Break????????
 
Still no luck

"Jim Cone" wrote:


Try...
ActiveSheet.Columns("S").PageBreak = xlPageBreakManual
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins: sorting, comparing, matching, listing, finding...)



"Kiba"
wrote in message
So I need to set a vertical page break and this is the macro I'm running but
it errors at this line.

Set ActiveSheet.VPageBreaks(1).Location = Range("S1")

Here's the full macro
__________________________________________________ _____________
Sub Test()
Set ActiveSheet.VPageBreaks(1).Location = Range("S1")
-snip-
End Sub

Jim Cone
Page Break????????
 

What does no luck mean?
It worked for me.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

"Kiba"
wrote in message
....
Still no luck



"Jim Cone" wrote:
Try...
ActiveSheet.Columns("S").PageBreak = xlPageBreakManual
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins: sorting, comparing, matching, listing, finding...)



"Kiba"
wrote in message
So I need to set a vertical page break and this is the macro I'm running but
it errors at this line.

Set ActiveSheet.VPageBreaks(1).Location = Range("S1")

Here's the full macro
________________________________________
Sub Test()
Set ActiveSheet.VPageBreaks(1).Location = Range("S1")
-snip-
End Sub

Kiba
Page Break????????
 
Sorry, It's inserting a page break just not where I want it to. I only want
one its inserting 2

"Jim Cone" wrote:


What does no luck mean?
It worked for me.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

"Kiba"
wrote in message
....
Still no luck



"Jim Cone" wrote:
Try...
ActiveSheet.Columns("S").PageBreak = xlPageBreakManual
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins: sorting, comparing, matching, listing, finding...)



"Kiba"
wrote in message
So I need to set a vertical page break and this is the macro I'm running but
it errors at this line.

Set ActiveSheet.VPageBreaks(1).Location = Range("S1")

Here's the full macro
________________________________________
Sub Test()
Set ActiveSheet.VPageBreaks(1).Location = Range("S1")
-snip-
End Sub

Kiba
Page Break????????
 
Here's my entire code its kinda long.
__________________________________________________ ________________

Sub Merge()

Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Delete the sheet "MergeSheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("MergeSheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "MergeSheet"
Set DestSh = ThisWorkbook.Worksheets.Add
DestSh.Name = "MergeSheet"

'loop through all worksheets and copy the data to the DestSh
For Each sh In ThisWorkbook.Worksheets
If sh.Name < DestSh.Name Then
Last = LastRow(DestSh)

'This example copies everything, if you only want to copy
'values/formats look at the example below this macro

sh.UsedRange.Copy DestSh.Cells(Last + 1, "A")

'This will copy the sheet name in the Q column if you want
'DestSh.Cells(Last + 1, "s").Value = sh.Name



End If
Next

Application.Goto DestSh.Cells(1)

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

Macro1
Macro2
HideRows
Macro4
Test

End Sub
__________________________________________________ _________________
Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


Function LastCol(sh As Worksheet)
On Error Resume Next
LastCol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function



__________________________________________________ __

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/8/2007 by Daniel L Wilson
'

'
Range("C3:C65536").Select
Range("A3:Z65536").Sort Key1:=Range("D3"), Order1:=xlAscending,
Key2:=Range( _
"A3"), Order2:=xlAscending, Key3:=Range("E3"), Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
ActiveWindow.SmallScroll Down:=0
End Sub
__________________________________________________ _____
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 6/8/2007 by Daniel L Wilson
'

'
Columns("A:A").Select
Selection.ColumnWidth = 17.29
Columns("B:B").Select
Selection.ColumnWidth = 9.43
Columns("C:C").Select
Selection.ColumnWidth = 12.43
Columns("D:D").Select
Selection.ColumnWidth = 5.14
Columns("E:E").Select
Selection.ColumnWidth = 24.86
Columns("F:F").Select
Selection.ColumnWidth = 16.86
Columns("G:G").Select
Selection.ColumnWidth = 19.14
Columns("H:H").Select
Selection.ColumnWidth = 9.87
Columns("I:I").Select
Selection.ColumnWidth = 11
Columns("J:J").Select
Selection.ColumnWidth = 34.43
Columns("K:K").Select
Selection.ColumnWidth = 13.57
Columns("L:O").Select
Selection.ColumnWidth = 11
ActiveWindow.ScrollColumn = 7
Columns("P:P").Select
Selection.ColumnWidth = 17.29
Columns("Q:R").Select
Selection.ColumnWidth = 7.43
Range("A1:Z65536").Select
Selection.Rows.AutoFit

End Sub

________________________________________________
'HIDE ROWS BLANK ROWS ON REPORT SHEEt
Sub HideRows()

Application.ScreenUpdating = False
With ActiveSheet.UsedRange

.Rows.Hidden = False
For Each cell In Range("B3:B65536")
If cell.Value = "DATE" Then _
cell.EntireRow.Hidden = True
Next cell

End With

With ActiveSheet.UsedRange

For Each cell In Range("A3:A65536")
If cell.Value = "CUSTOMER" Then _
cell.EntireRow.Hidden = True
Next cell
End With

End Sub
______________________________________


Sub Macro4()
'
' Macro4 Macro
' Macro recorded 6/8/2007 by Daniel L Wilson
'

'
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = "Created: &D, &T"
.CenterHeader = "Tarkett Complaint Log" & Chr(10) & "Master List"
.RightHeader = "&P/&N"
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.05)
.RightMargin = Application.InchesToPoints(0.05)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.1)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.PrintHeadings = False
.PrintGridlines = True
.PrintComments = xlPrintNoComments
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 2
.FitToPagesTall = 100
.PrintErrors = xlPrintErrorsDisplayed
End With
End Sub
__________________________________________________ ____
Sub Test()
'
' Test Macro
' Macro recorded 7/12/2007 by Daniel L Wilson
'

' Sheets("Report").Select
' Range("G27").Activate
' ActiveWindow.SelectedSheets.HPageBreaks.Add befo=ActiveCell
' ActiveSheet.Columns("S").PageBreak = xlPageBreakManual
ActiveSheet.ResetAllPageBreaks
ActiveSheet.VPageBreaks(1).Location = Range("S1")
'Set ActiveSheet.VPageBreaks.Location = Range("S1")

Range("T3").Select
ActiveCell.FormulaR1C1 = "=SUM(K:K)"
Range("T3:X3").Select
Selection.FillRight
Range("K1:O2").Select
Selection.Copy
Range("T1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("S3").Select
ActiveCell.FormulaR1C1 = "Tarkett Totals"
With ActiveCell.Characters(Start:=1, Length:=14).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Columns("S:S").EntireColumn.AutoFit
Columns("T:T").EntireColumn.AutoFit
Columns("U:U").EntireColumn.AutoFit
Columns("V:V").EntireColumn.AutoFit
Columns("X:X").EntireColumn.AutoFit
Columns("W:W").EntireColumn.AutoFit
Columns("X:X").EntireColumn.AutoFit
Range("S1:X3").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub

Jim Cone
Page Break????????
 

Maybe the "other" page break is an automatic one?
They look slightly different.
--
Jim Cone
San Francisco, USA



"Kiba"
wrote in message
Sorry, It's inserting a page break just not where I want it to. I only want
one its inserting 2



"Jim Cone" wrote:
What does no luck mean?
It worked for me.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Kiba"
wrote in message
....
Still no luck



"Jim Cone" wrote:
Try...
ActiveSheet.Columns("S").PageBreak = xlPageBreakManual
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins: sorting, comparing, matching, listing, finding...)



"Kiba"
wrote in message
So I need to set a vertical page break and this is the macro I'm running but
it errors at this line.

Set ActiveSheet.VPageBreaks(1).Location = Range("S1")

Here's the full macro
________________________________________
Sub Test()
Set ActiveSheet.VPageBreaks(1).Location = Range("S1")
-snip-
End Sub

JLGWhiz
Page Break????????
 
I am not sure what you are trying to do, but the code below put a vertical
page break to the left of column M. That is where it is supposed to go.

"Kiba" wrote:

Here's my entire code its kinda long.
__________________________________________________ ________________

Sub Merge()

Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Delete the sheet "MergeSheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("MergeSheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "MergeSheet"
Set DestSh = ThisWorkbook.Worksheets.Add
DestSh.Name = "MergeSheet"

'loop through all worksheets and copy the data to the DestSh
For Each sh In ThisWorkbook.Worksheets
If sh.Name < DestSh.Name Then
Last = LastRow(DestSh)

'This example copies everything, if you only want to copy
'values/formats look at the example below this macro

sh.UsedRange.Copy DestSh.Cells(Last + 1, "A")

'This will copy the sheet name in the Q column if you want
'DestSh.Cells(Last + 1, "s").Value = sh.Name



End If
Next

Application.Goto DestSh.Cells(1)

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

Macro1
Macro2
HideRows
Macro4
Test

End Sub
__________________________________________________ _________________
Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


Function LastCol(sh As Worksheet)
On Error Resume Next
LastCol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function



__________________________________________________ __

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/8/2007 by Daniel L Wilson
'

'
Range("C3:C65536").Select
Range("A3:Z65536").Sort Key1:=Range("D3"), Order1:=xlAscending,
Key2:=Range( _
"A3"), Order2:=xlAscending, Key3:=Range("E3"), Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
ActiveWindow.SmallScroll Down:=0
End Sub
__________________________________________________ _____
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 6/8/2007 by Daniel L Wilson
'

'
Columns("A:A").Select
Selection.ColumnWidth = 17.29
Columns("B:B").Select
Selection.ColumnWidth = 9.43
Columns("C:C").Select
Selection.ColumnWidth = 12.43
Columns("D:D").Select
Selection.ColumnWidth = 5.14
Columns("E:E").Select
Selection.ColumnWidth = 24.86
Columns("F:F").Select
Selection.ColumnWidth = 16.86
Columns("G:G").Select
Selection.ColumnWidth = 19.14
Columns("H:H").Select
Selection.ColumnWidth = 9.87
Columns("I:I").Select
Selection.ColumnWidth = 11
Columns("J:J").Select
Selection.ColumnWidth = 34.43
Columns("K:K").Select
Selection.ColumnWidth = 13.57
Columns("L:O").Select
Selection.ColumnWidth = 11
ActiveWindow.ScrollColumn = 7
Columns("P:P").Select
Selection.ColumnWidth = 17.29
Columns("Q:R").Select
Selection.ColumnWidth = 7.43
Range("A1:Z65536").Select
Selection.Rows.AutoFit

End Sub

________________________________________________
'HIDE ROWS BLANK ROWS ON REPORT SHEEt
Sub HideRows()

Application.ScreenUpdating = False
With ActiveSheet.UsedRange

.Rows.Hidden = False
For Each cell In Range("B3:B65536")
If cell.Value = "DATE" Then _
cell.EntireRow.Hidden = True
Next cell

End With

With ActiveSheet.UsedRange

For Each cell In Range("A3:A65536")
If cell.Value = "CUSTOMER" Then _
cell.EntireRow.Hidden = True
Next cell
End With

End Sub
______________________________________


Sub Macro4()
'
' Macro4 Macro
' Macro recorded 6/8/2007 by Daniel L Wilson
'

'
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = "Created: &D, &T"
.CenterHeader = "Tarkett Complaint Log" & Chr(10) & "Master List"
.RightHeader = "&P/&N"
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.05)
.RightMargin = Application.InchesToPoints(0.05)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.1)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.PrintHeadings = False
.PrintGridlines = True
.PrintComments = xlPrintNoComments
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 2
.FitToPagesTall = 100
.PrintErrors = xlPrintErrorsDisplayed
End With
End Sub
__________________________________________________ ____
Sub Test()
'
' Test Macro
' Macro recorded 7/12/2007 by Daniel L Wilson
'

' Sheets("Report").Select
' Range("G27").Activate
' ActiveWindow.SelectedSheets.HPageBreaks.Add befo=ActiveCell
' ActiveSheet.Columns("S").PageBreak = xlPageBreakManual
ActiveSheet.ResetAllPageBreaks
ActiveSheet.VPageBreaks(1).Location = Range("S1")
'Set ActiveSheet.VPageBreaks.Location = Range("S1")

Range("T3").Select
ActiveCell.FormulaR1C1 = "=SUM(K:K)"
Range("T3:X3").Select
Selection.FillRight
Range("K1:O2").Select
Selection.Copy
Range("T1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("S3").Select
ActiveCell.FormulaR1C1 = "Tarkett Totals"
With ActiveCell.Characters(Start:=1, Length:=14).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Columns("S:S").EntireColumn.AutoFit
Columns("T:T").EntireColumn.AutoFit
Columns("U:U").EntireColumn.AutoFit
Columns("V:V").EntireColumn.AutoFit
Columns("X:X").EntireColumn.AutoFit
Columns("W:W").EntireColumn.AutoFit
Columns("X:X").EntireColumn.AutoFit
Range("S1:X3").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub

JLGWhiz
Page Break????????
 
Probably would help to include the code:

Sub pgbk()
ActiveSheet.VPageBreaks(1).Location = Sheets(1).Range("M1")
End Sub


"Kiba" wrote:

Here's my entire code its kinda long.
__________________________________________________ ________________

Sub Merge()

Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Delete the sheet "MergeSheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("MergeSheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "MergeSheet"
Set DestSh = ThisWorkbook.Worksheets.Add
DestSh.Name = "MergeSheet"

'loop through all worksheets and copy the data to the DestSh
For Each sh In ThisWorkbook.Worksheets
If sh.Name < DestSh.Name Then
Last = LastRow(DestSh)

'This example copies everything, if you only want to copy
'values/formats look at the example below this macro

sh.UsedRange.Copy DestSh.Cells(Last + 1, "A")

'This will copy the sheet name in the Q column if you want
'DestSh.Cells(Last + 1, "s").Value = sh.Name



End If
Next

Application.Goto DestSh.Cells(1)

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

Macro1
Macro2
HideRows
Macro4
Test

End Sub
__________________________________________________ _________________
Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


Function LastCol(sh As Worksheet)
On Error Resume Next
LastCol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function



__________________________________________________ __

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/8/2007 by Daniel L Wilson
'

'
Range("C3:C65536").Select
Range("A3:Z65536").Sort Key1:=Range("D3"), Order1:=xlAscending,
Key2:=Range( _
"A3"), Order2:=xlAscending, Key3:=Range("E3"), Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
ActiveWindow.SmallScroll Down:=0
End Sub
__________________________________________________ _____
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 6/8/2007 by Daniel L Wilson
'

'
Columns("A:A").Select
Selection.ColumnWidth = 17.29
Columns("B:B").Select
Selection.ColumnWidth = 9.43
Columns("C:C").Select
Selection.ColumnWidth = 12.43
Columns("D:D").Select
Selection.ColumnWidth = 5.14
Columns("E:E").Select
Selection.ColumnWidth = 24.86
Columns("F:F").Select
Selection.ColumnWidth = 16.86
Columns("G:G").Select
Selection.ColumnWidth = 19.14
Columns("H:H").Select
Selection.ColumnWidth = 9.87
Columns("I:I").Select
Selection.ColumnWidth = 11
Columns("J:J").Select
Selection.ColumnWidth = 34.43
Columns("K:K").Select
Selection.ColumnWidth = 13.57
Columns("L:O").Select
Selection.ColumnWidth = 11
ActiveWindow.ScrollColumn = 7
Columns("P:P").Select
Selection.ColumnWidth = 17.29
Columns("Q:R").Select
Selection.ColumnWidth = 7.43
Range("A1:Z65536").Select
Selection.Rows.AutoFit

End Sub

________________________________________________
'HIDE ROWS BLANK ROWS ON REPORT SHEEt
Sub HideRows()

Application.ScreenUpdating = False
With ActiveSheet.UsedRange

.Rows.Hidden = False
For Each cell In Range("B3:B65536")
If cell.Value = "DATE" Then _
cell.EntireRow.Hidden = True
Next cell

End With

With ActiveSheet.UsedRange

For Each cell In Range("A3:A65536")
If cell.Value = "CUSTOMER" Then _
cell.EntireRow.Hidden = True
Next cell
End With

End Sub
______________________________________


Sub Macro4()
'
' Macro4 Macro
' Macro recorded 6/8/2007 by Daniel L Wilson
'

'
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = "Created: &D, &T"
.CenterHeader = "Tarkett Complaint Log" & Chr(10) & "Master List"
.RightHeader = "&P/&N"
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.05)
.RightMargin = Application.InchesToPoints(0.05)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.1)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.PrintHeadings = False
.PrintGridlines = True
.PrintComments = xlPrintNoComments
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 2
.FitToPagesTall = 100
.PrintErrors = xlPrintErrorsDisplayed
End With
End Sub
__________________________________________________ ____
Sub Test()
'
' Test Macro
' Macro recorded 7/12/2007 by Daniel L Wilson
'

' Sheets("Report").Select
' Range("G27").Activate
' ActiveWindow.SelectedSheets.HPageBreaks.Add befo=ActiveCell
' ActiveSheet.Columns("S").PageBreak = xlPageBreakManual
ActiveSheet.ResetAllPageBreaks
ActiveSheet.VPageBreaks(1).Location = Range("S1")
'Set ActiveSheet.VPageBreaks.Location = Range("S1")

Range("T3").Select
ActiveCell.FormulaR1C1 = "=SUM(K:K)"
Range("T3:X3").Select
Selection.FillRight
Range("K1:O2").Select
Selection.Copy
Range("T1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("S3").Select
ActiveCell.FormulaR1C1 = "Tarkett Totals"
With ActiveCell.Characters(Start:=1, Length:=14).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Columns("S:S").EntireColumn.AutoFit
Columns("T:T").EntireColumn.AutoFit
Columns("U:U").EntireColumn.AutoFit
Columns("V:V").EntireColumn.AutoFit
Columns("X:X").EntireColumn.AutoFit
Columns("W:W").EntireColumn.AutoFit
Columns("X:X").EntireColumn.AutoFit
Range("S1:X3").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub

Kalyani
Page Break????????
 
This statement results in a com exception.
im changing the location for Horizontal pagebreaks.
please help if i can delete the Hpagebreak and insert at a new position.
im not able to delete and able to add a new one.
even location change is not working.
Thanks in advance

"JLGWhiz" wrote:

Probably would help to include the code:

Sub pgbk()
ActiveSheet.VPageBreaks(1).Location = Sheets(1).Range("M1")
End Sub


"Kiba" wrote:

Here's my entire code its kinda long.
__________________________________________________ ________________

Sub Merge()

Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Delete the sheet "MergeSheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("MergeSheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "MergeSheet"
Set DestSh = ThisWorkbook.Worksheets.Add
DestSh.Name = "MergeSheet"

'loop through all worksheets and copy the data to the DestSh
For Each sh In ThisWorkbook.Worksheets
If sh.Name < DestSh.Name Then
Last = LastRow(DestSh)

'This example copies everything, if you only want to copy
'values/formats look at the example below this macro

sh.UsedRange.Copy DestSh.Cells(Last + 1, "A")

'This will copy the sheet name in the Q column if you want
'DestSh.Cells(Last + 1, "s").Value = sh.Name



End If
Next

Application.Goto DestSh.Cells(1)

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

Macro1
Macro2
HideRows
Macro4
Test

End Sub
__________________________________________________ _________________
Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


Function LastCol(sh As Worksheet)
On Error Resume Next
LastCol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function



__________________________________________________ __

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/8/2007 by Daniel L Wilson
'

'
Range("C3:C65536").Select
Range("A3:Z65536").Sort Key1:=Range("D3"), Order1:=xlAscending,
Key2:=Range( _
"A3"), Order2:=xlAscending, Key3:=Range("E3"), Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
ActiveWindow.SmallScroll Down:=0
End Sub
__________________________________________________ _____
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 6/8/2007 by Daniel L Wilson
'

'
Columns("A:A").Select
Selection.ColumnWidth = 17.29
Columns("B:B").Select
Selection.ColumnWidth = 9.43
Columns("C:C").Select
Selection.ColumnWidth = 12.43
Columns("D:D").Select
Selection.ColumnWidth = 5.14
Columns("E:E").Select
Selection.ColumnWidth = 24.86
Columns("F:F").Select
Selection.ColumnWidth = 16.86
Columns("G:G").Select
Selection.ColumnWidth = 19.14
Columns("H:H").Select
Selection.ColumnWidth = 9.87
Columns("I:I").Select
Selection.ColumnWidth = 11
Columns("J:J").Select
Selection.ColumnWidth = 34.43
Columns("K:K").Select
Selection.ColumnWidth = 13.57
Columns("L:O").Select
Selection.ColumnWidth = 11
ActiveWindow.ScrollColumn = 7
Columns("P:P").Select
Selection.ColumnWidth = 17.29
Columns("Q:R").Select
Selection.ColumnWidth = 7.43
Range("A1:Z65536").Select
Selection.Rows.AutoFit

End Sub

________________________________________________
'HIDE ROWS BLANK ROWS ON REPORT SHEEt
Sub HideRows()

Application.ScreenUpdating = False
With ActiveSheet.UsedRange

.Rows.Hidden = False
For Each cell In Range("B3:B65536")
If cell.Value = "DATE" Then _
cell.EntireRow.Hidden = True
Next cell

End With

With ActiveSheet.UsedRange

For Each cell In Range("A3:A65536")
If cell.Value = "CUSTOMER" Then _
cell.EntireRow.Hidden = True
Next cell
End With

End Sub
______________________________________


Sub Macro4()
'
' Macro4 Macro
' Macro recorded 6/8/2007 by Daniel L Wilson
'

'
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = "Created: &D, &T"
.CenterHeader = "Tarkett Complaint Log" & Chr(10) & "Master List"
.RightHeader = "&P/&N"
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.05)
.RightMargin = Application.InchesToPoints(0.05)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.1)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.PrintHeadings = False
.PrintGridlines = True
.PrintComments = xlPrintNoComments
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 2
.FitToPagesTall = 100
.PrintErrors = xlPrintErrorsDisplayed
End With
End Sub
__________________________________________________ ____
Sub Test()
'
' Test Macro
' Macro recorded 7/12/2007 by Daniel L Wilson
'

' Sheets("Report").Select
' Range("G27").Activate
' ActiveWindow.SelectedSheets.HPageBreaks.Add befo=ActiveCell
' ActiveSheet.Columns("S").PageBreak = xlPageBreakManual
ActiveSheet.ResetAllPageBreaks
ActiveSheet.VPageBreaks(1).Location = Range("S1")
'Set ActiveSheet.VPageBreaks.Location = Range("S1")

Range("T3").Select
ActiveCell.FormulaR1C1 = "=SUM(K:K)"
Range("T3:X3").Select
Selection.FillRight
Range("K1:O2").Select
Selection.Copy
Range("T1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("S3").Select
ActiveCell.FormulaR1C1 = "Tarkett Totals"
With ActiveCell.Characters(Start:=1, Length:=14).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Columns("S:S").EntireColumn.AutoFit
Columns("T:T").EntireColumn.AutoFit
Columns("U:U").EntireColumn.AutoFit
Columns("V:V").EntireColumn.AutoFit
Columns("X:X").EntireColumn.AutoFit
Columns("W:W").EntireColumn.AutoFit
Columns("X:X").EntireColumn.AutoFit
Range("S1:X3").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic


All times are GMT +1. The time now is 02:00 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com