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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,549
Office Version
  1. 365
Platform
  1. Windows
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
 

Amelchor0425

New Member
Joined
Sep 4, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Thank for your response and welcome, much appreciated. With the update I get the following error:
2020-09-04_7-56-03.jpg
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,549
Office Version
  1. 365
Platform
  1. Windows
Did you make any changes to the code I supplied?
 

Amelchor0425

New Member
Joined
Sep 4, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Operator error, it worked. Thanks a million!! <doing my happy dance>
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,549
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,588
Messages
5,625,668
Members
416,125
Latest member
NeedExcelHelp2021

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
Top