- Sep 4, 2020
- Office Version
Have several workbooks in a assigned folder, macro runs great in opening workbooks and renaming first tab. Now, I would like to modify macro to change the color of the worksheet that was renamed, below is the macro used to rename. As you can see, when color tab is the issue.
Sub RenSheetsSelections() Dim MyFolder As String Dim MyFile As String Dim wbname As String MyFolder = "C:myfolderpath" MyFile = Dir(MyFolder & "\*.xls") Application.ScreenUpdating = False Do While MyFile <> "" Workbooks.Open Filename:=MyFolder & "\" & MyFile With ActiveWorkbook wbname = Left(.Name, InStr(.Name, ".") - 1) .Sheets(1).Name = " test " With ActiveWorkbook.Sheets("test").Tab .Color = 5287936 .TintAndShade = 0 .Close savechanges:=True End With MyFile = Dir Loop Application.ScreenUpdating = True MsgBox "Done" End Sub