Another Excel formatting question

[fredalina]

This one is strange.

i got my code to work for C:\Sample.XLS for the workbook Name1 (thanks to all who helped). i attempted to do the same formatting for workbook Name2, and the strangest thing happened (probably not strange to you, but strange to me). The code ran through to the Exit Sub line, and the Name1 tab is formatted correctly, but the Name2 tab is formatted only for the first line of formatting code (bold). The other two attempted formats (autofit and cell color) did not go through for Name2. When adding Name3 to the code identical to Name2, it was formatted the same as Name2.

Can anyone help me? Also, is it perhaps possible to do all worksheets at one time?

Thanks!

strWkbkName = "C:\Sample.xls"
MsgBox "strWkbkName is " & strWkbkName
strWkSt = "Name1"
Set objXL = CreateObject("Excel.Application")
objXL.Workbooks.Open (strWkbkName)
With objXL
.Worksheets(strWkSt) _
.Rows("1:1").Font.Bold = True
.Range("A1:M1").Interior.ColorIndex = 15
.Columns("A:M").Autofit
End With

strWkSt = "Name2"

With objXL
.Worksheets(strWkSt) _
.Rows("1:1").Font.Bold = True
.Range("A1:M1").Interior.ColorIndex = 15
.Columns("A:M").Autofit
End With

strWkSt = "Name3"
With objXL
.Worksheets(strWkSt) _
.Rows("1:1").Font.Bold = True
.Range("A1:M1").Interior.ColorIndex = 15
.Columns("A:M").Autofit
End With
objXL.DisplayAlerts = False
objXL.Save
objXL.DisplayAlerts = False
objXL.Workbooks.Close
objXL.DisplayAlerts = False
objXL.Quit
Set objXL = Nothing

Exit Sub

 

[DJkarl]

This one is strange.

i got my code to work for C:\Sample.XLS for the workbook Name1 (thanks to all who helped). i attempted to do the same formatting for workbook Name2, and the strangest thing happened (probably not strange to you, but strange to me). The code ran through to the Exit Sub line, and the Name1 tab is formatted correctly, but the Name2 tab is formatted only for the first line of formatting code (bold). The other two attempted formats (autofit and cell color) did not go through for Name2. When adding Name3 to the code identical to Name2, it was formatted the same as Name2.

Can anyone help me? Also, is it perhaps possible to do all worksheets at one time?

Thanks!

Only the first line is formatted because that's the only time you explicitly reference that worksheet.

The code in red shows you refer to row 1 in a specific worksheet, the code in blue does not contain the same worksheets reference therefore it will format the currently Active worksheet which is likely the first worksheet.

You can either set the active worksheet to the one you want to work on, or add the .Worksheets(strWkSt) _ line for each formatting call.

strWkSt = "Name2"

With objXL
[COLOR=red].Worksheets(strWkSt) _
.Rows("1:1").Font.Bold = True
[/COLOR][COLOR=blue].Range("A1:M1").Interior.ColorIndex = 15
.Columns("A:M").Autofit[/COLOR]
End With

 

[fredalina]

OK, thanks! Will try that.

 

[fredalina]

It works beautifully. Thanks again!