Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
error in code to format only a few sheets in workbook
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook? | Excel Worksheet Functions | |||
How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook? | Excel Programming | |||
VBA Code To have a macro repeat on all sheets in a workbook | Excel Worksheet Functions | |||
run code on opening workbook and apply code to certain sheets | Excel Programming | |||
How to hide sheets and VBA code in my Excel workbook? | Excel Programming |