ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Worksheet formatting (https://www.excelbanter.com/excel-worksheet-functions/96369-worksheet-formatting.html)

JOUIOUI
Worksheet formatting
 
I've posted this question before so I apologize for the repetition but I
still don't have the answer I need that is why I'm reposting with additional
information.

I need column headings and page setup to be the same on all my worksheets in
one workbook and I can't get it to work. I've been told to use "option
explicit" but I can't get it to work. Below is some of my code. Any
suggestions on how I can get the page formatting that begins with the code of
"With ActiveSheet" to appear on all these sheets whon below, thanks

Sub AddSheets()

' Add new sheets to right of All Records Sheet

Dim NewSheets As Variant
Dim i As Long

NewSheets = Array("CONFIRM NO MATCHES", _
"GESA CARD MATCHES", "GESA CARD NO MATCHES")
For i = UBound(NewSheets) To LBound(NewSheets) Step -1
Sheets.Add after:=Sheets(1)
ActiveSheet.Name = NewSheets(i)
Next i

End Sub

Sub ConfirmNoMatches()

' Create New Confirm Report

Dim rng As Range, cell As Range

Dim i As Long, sh As Worksheet
With Worksheets("All Records")
Set rng = .Range(.Cells(1, 1), _
.Cells(Rows.Count, 1).End(xlUp))
End With
i = 1

Set sh = Worksheets("CONFIRM NO MATCHES")
For Each cell In rng
If UCase(Trim(cell.Value)) = "NO MATCH TO ANY GES" And _
UCase(Trim(cell.Offset(0, 1).Value)) = "CONFIRM" Then
cell.EntireRow.Copy sh.Cells(i, 1)
i = i + 1
End If

Next

With ActiveSheet

Dim xLastrow As Long

xLastrow = .Cells(.Rows.Count, 1).End(xlUp).Row

.Cells(xLastrow + 2, 5) = "Total"
.Cells(xLastrow + 2, 5).Font.Bold = True
.Cells(xLastrow + 2, 6).Formula = "=sum(F2:F" & xLastrow & ")"
.Cells(xLastrow + 2, 6).Font.Bold = True

.Rows(1).Insert
.Range("A1").Value = "Match/No Match"
.Range("B1").Value = "Original Table"
.Range("C1").Value = "CNO"
.Range("D1").Value = "Name"
.Range("E1").Value = "Date"
.Range("F1").Value = "Amount"

Columns("A:A").ColumnWidth = 27.71
Columns("B:B").ColumnWidth = 14.86
Columns("C:C").ColumnWidth = 17.86
Columns("D:D").ColumnWidth = 20.86
Columns("E:E").ColumnWidth = 11.29
Columns("F:F").ColumnWidth = 14.1

Columns("A:F").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


Range("A1:F1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Font.Bold = True
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Selection.Interior.ColorIndex = 37
Rows("1:1").RowHeight = 24.75
Cells.Select
End With
Range("D8").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "Confirm No Matches" & Chr(10) & "&D"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = "&P"
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = True
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 85
.PrintErrors = xlPrintErrorsDisplayed

Range("A1:F80").Sort Key1:=Range("D8"), Order1:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers

End With
End Sub

Marcelo
Worksheet formatting
 
Hi,

try to right click on the sheets tab, select all, format and include the
headings as you want, excel will assume it for all sheets selected on your
workbook

hth
regards from Brazil
Marcelo

"JOUIOUI" escreveu:

I've posted this question before so I apologize for the repetition but I
still don't have the answer I need that is why I'm reposting with additional
information.

I need column headings and page setup to be the same on all my worksheets in
one workbook and I can't get it to work. I've been told to use "option
explicit" but I can't get it to work. Below is some of my code. Any
suggestions on how I can get the page formatting that begins with the code of
"With ActiveSheet" to appear on all these sheets whon below, thanks

Sub AddSheets()

' Add new sheets to right of All Records Sheet

Dim NewSheets As Variant
Dim i As Long

NewSheets = Array("CONFIRM NO MATCHES", _
"GESA CARD MATCHES", "GESA CARD NO MATCHES")
For i = UBound(NewSheets) To LBound(NewSheets) Step -1
Sheets.Add after:=Sheets(1)
ActiveSheet.Name = NewSheets(i)
Next i

End Sub

Sub ConfirmNoMatches()

' Create New Confirm Report

Dim rng As Range, cell As Range

Dim i As Long, sh As Worksheet
With Worksheets("All Records")
Set rng = .Range(.Cells(1, 1), _
.Cells(Rows.Count, 1).End(xlUp))
End With
i = 1

Set sh = Worksheets("CONFIRM NO MATCHES")
For Each cell In rng
If UCase(Trim(cell.Value)) = "NO MATCH TO ANY GES" And _
UCase(Trim(cell.Offset(0, 1).Value)) = "CONFIRM" Then
cell.EntireRow.Copy sh.Cells(i, 1)
i = i + 1
End If

Next

With ActiveSheet

Dim xLastrow As Long

xLastrow = .Cells(.Rows.Count, 1).End(xlUp).Row

.Cells(xLastrow + 2, 5) = "Total"
.Cells(xLastrow + 2, 5).Font.Bold = True
.Cells(xLastrow + 2, 6).Formula = "=sum(F2:F" & xLastrow & ")"
.Cells(xLastrow + 2, 6).Font.Bold = True

.Rows(1).Insert
.Range("A1").Value = "Match/No Match"
.Range("B1").Value = "Original Table"
.Range("C1").Value = "CNO"
.Range("D1").Value = "Name"
.Range("E1").Value = "Date"
.Range("F1").Value = "Amount"

Columns("A:A").ColumnWidth = 27.71
Columns("B:B").ColumnWidth = 14.86
Columns("C:C").ColumnWidth = 17.86
Columns("D:D").ColumnWidth = 20.86
Columns("E:E").ColumnWidth = 11.29
Columns("F:F").ColumnWidth = 14.1

Columns("A:F").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


Range("A1:F1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Font.Bold = True
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Selection.Interior.ColorIndex = 37
Rows("1:1").RowHeight = 24.75
Cells.Select
End With
Range("D8").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "Confirm No Matches" & Chr(10) & "&D"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = "&P"
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = True
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 85
.PrintErrors = xlPrintErrorsDisplayed

Range("A1:F80").Sort Key1:=Range("D8"), Order1:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers

End With
End Sub

JOUIOUI
Worksheet formatting
 
I need to automate this with code, how would I do that?

"Marcelo" wrote:

Hi,

try to right click on the sheets tab, select all, format and include the
headings as you want, excel will assume it for all sheets selected on your
workbook

hth
regards from Brazil
Marcelo

"JOUIOUI" escreveu:

I've posted this question before so I apologize for the repetition but I
still don't have the answer I need that is why I'm reposting with additional
information.

I need column headings and page setup to be the same on all my worksheets in
one workbook and I can't get it to work. I've been told to use "option
explicit" but I can't get it to work. Below is some of my code. Any
suggestions on how I can get the page formatting that begins with the code of
"With ActiveSheet" to appear on all these sheets whon below, thanks

Sub AddSheets()

' Add new sheets to right of All Records Sheet

Dim NewSheets As Variant
Dim i As Long

NewSheets = Array("CONFIRM NO MATCHES", _
"GESA CARD MATCHES", "GESA CARD NO MATCHES")
For i = UBound(NewSheets) To LBound(NewSheets) Step -1
Sheets.Add after:=Sheets(1)
ActiveSheet.Name = NewSheets(i)
Next i

End Sub

Sub ConfirmNoMatches()

' Create New Confirm Report

Dim rng As Range, cell As Range

Dim i As Long, sh As Worksheet
With Worksheets("All Records")
Set rng = .Range(.Cells(1, 1), _
.Cells(Rows.Count, 1).End(xlUp))
End With
i = 1

Set sh = Worksheets("CONFIRM NO MATCHES")
For Each cell In rng
If UCase(Trim(cell.Value)) = "NO MATCH TO ANY GES" And _
UCase(Trim(cell.Offset(0, 1).Value)) = "CONFIRM" Then
cell.EntireRow.Copy sh.Cells(i, 1)
i = i + 1
End If

Next

With ActiveSheet

Dim xLastrow As Long

xLastrow = .Cells(.Rows.Count, 1).End(xlUp).Row

.Cells(xLastrow + 2, 5) = "Total"
.Cells(xLastrow + 2, 5).Font.Bold = True
.Cells(xLastrow + 2, 6).Formula = "=sum(F2:F" & xLastrow & ")"
.Cells(xLastrow + 2, 6).Font.Bold = True

.Rows(1).Insert
.Range("A1").Value = "Match/No Match"
.Range("B1").Value = "Original Table"
.Range("C1").Value = "CNO"
.Range("D1").Value = "Name"
.Range("E1").Value = "Date"
.Range("F1").Value = "Amount"

Columns("A:A").ColumnWidth = 27.71
Columns("B:B").ColumnWidth = 14.86
Columns("C:C").ColumnWidth = 17.86
Columns("D:D").ColumnWidth = 20.86
Columns("E:E").ColumnWidth = 11.29
Columns("F:F").ColumnWidth = 14.1

Columns("A:F").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


Range("A1:F1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Font.Bold = True
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Selection.Interior.ColorIndex = 37
Rows("1:1").RowHeight = 24.75
Cells.Select
End With
Range("D8").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "Confirm No Matches" & Chr(10) & "&D"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = "&P"
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = True
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 85
.PrintErrors = xlPrintErrorsDisplayed

Range("A1:F80").Sort Key1:=Range("D8"), Order1:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers

End With
End Sub


All times are GMT +1. The time now is 03:44 AM.

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