Thread: error in code to format only a few sheets in workbook

I'm using this code to add 2 columns to the left to only a few of my sheets
in my workbook but it is not working. I'm calling only the sheets I want to
add the code to but I'm getting the error of "Wrong Number of Arguements or
invalid property assignment". What am I doing wrong here. Thanks

Sub AddColumnsToLeft()

' Insert 2 columns to the far left with gridlines and column headings to
specific sheets

Sheets("NEW CONFIRM REPORT", "GESV CARD NO MATCHES", "GESA CHECK NO
MATCHES", _
"GESA CARD NO MATCHES").Select

Columns("A:B").Select
Selection.Insert Shift:=xlToRight

Columns("A:A").ColumnWidth = 9.5
Columns("B:B").ColumnWidth = 25
Columns("D:D").ColumnWidth = 10.71
Columns("C:C").ColumnWidth = 10

Range("A1").Select
ActiveCell.FormulaR1C1 = "TRAN CD"
Range("B1").Select
ActiveCell.FormulaR1C1 = "COMMENTS/NOTES"

ActiveSheet.PageSetUp.PrintArea = "$A:$J"

Columns("A:J").Select
Columns("A:J").Borders.LineStyle = xlContinuous

Columns("B:B").Select
With Selection
.WrapText = True

End With
End Sub

Instead of selecting all the sheets at once, why not put the formatting code
in a separate sub and call it for each sheet:

Sub AAAAA()
Call AddColumnsToLeft("NEW CONFIRM REPORT")
Call AddColumnsToLeft("GESV CARD NO MATCHES")
Call AddColumnsToLeft("GESA CHECK NO MATCHES")
Call AddColumnsToLeft("GESA CARD NO MATCHES")
End Sub

Sub AddColumnsToLeft(ShtName As String)
' Insert 2 columns to the far left with gridlines and column headings to
specific Sheets
Sheets(ShtName).Columns("A:B").Insert Shift:=xlToRight
Sheets(ShtName).Columns("A:A").ColumnWidth = 9.5
Sheets(ShtName).Columns("B:B").ColumnWidth = 25
Sheets(ShtName).Columns("D:D").ColumnWidth = 10.71
Sheets(ShtName).Columns("C:C").ColumnWidth = 10
Sheets(ShtName).Range("A1").FormulaR1C1 = "TRAN CD"
Sheets(ShtName).Range("B1").FormulaR1C1 = "COMMENTS/NOTES"
Sheets(ShtName).PageSetup.PrintArea = "$A:$J"
Sheets(ShtName).Columns("A:J").Borders.LineStyle = xlContinuous
Sheets(ShtName).Columns("B:B").WrapText = True
End Sub

This will work. I'm not sure why your code doesn't work, although I could
see problems trying to use ActiveCell and ActiveSheet when you have multiple
sheets selected.

Hope this helps,

Hutch

"JOUIOUI" wrote:

> I'm using this code to add 2 columns to the left to only a few of my sheets
> in my workbook but it is not working. I'm calling only the sheets I want to
> add the code to but I'm getting the error of "Wrong Number of Arguements or
> invalid property assignment". What am I doing wrong here. Thanks
>
> Sub AddColumnsToLeft()
>
> ' Insert 2 columns to the far left with gridlines and column headings to
> specific sheets
>
> Sheets("NEW CONFIRM REPORT", "GESV CARD NO MATCHES", "GESA CHECK NO
> MATCHES", _
> "GESA CARD NO MATCHES").Select
>
> Columns("A:B").Select
> Selection.Insert Shift:=xlToRight
>
> Columns("A:A").ColumnWidth = 9.5
> Columns("B:B").ColumnWidth = 25
> Columns("D:D").ColumnWidth = 10.71
> Columns("C:C").ColumnWidth = 10
>
> Range("A1").Select
> ActiveCell.FormulaR1C1 = "TRAN CD"
> Range("B1").Select
> ActiveCell.FormulaR1C1 = "COMMENTS/NOTES"
>
> ActiveSheet.PageSetUp.PrintArea = "$A:$J"
>
> Columns("A:J").Select
> Columns("A:J").Borders.LineStyle = xlContinuous
>
> Columns("B:B").Select
> With Selection
> .WrapText = True
>
> End With
> End Sub