error in code to format only a few sheets in workbook

J

JOUIOUI

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
 
T

Tom Hutchins

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
 

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

Top