VBA: Rename worksheet and color tab in assigned folder for multiple workbooks

Amelchor0425

New Member
Joined
Sep 4, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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.

VBA Code:
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Hi & welcome to MrExcel.
How about
VBA Code:
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 .Sheets(1)
            .Name = " test "
            .Tab.Color = 5287936
            .Tab.TintAndShade = 0
         End With
         .Close True
      End With
      
      MyFile = Dir
   Loop
   Application.ScreenUpdating = True
   
   MsgBox "Done"

End Sub
 
Upvote 0
Thank for your response and welcome, much appreciated. With the update I get the following error:
2020-09-04_7-56-03.jpg
 
Upvote 0
Did you make any changes to the code I supplied?
 
Upvote 0
Operator error, it worked. Thanks a million!! <doing my happy dance>
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top