Replace text in tab name with value from list and then save as a new file, replacing part of name with same value

bnbcat

New Member
Joined
Apr 27, 2016
Messages
41
Office Version
  1. 365
Platform
  1. Windows
Can someone help me with a code to do the following please?

1. Open a file - Actuals_Country.xlsx
2. Find the sheet with the name containing text "FY - COUNTRY"
3. Replace "COUNTRY" in the sheet name with the first value from a list in an external file (File name: List.xlsx, column A)
4. Save as a new file with the same name but replacing "Country" with the same value from the external list
5. Repeat steps 1-4 but replacing values with the next value in the external list

I have multiple macros that I found on Google, but 2 of them require a popup dialogue and the other keeps duplicating sheets. I'm not smart enough to try to pick it apart and put it back together :cry:
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Hi,

Check below code:

VBA Code:
Sub fileHandling()
Dim mainWB As Workbook
Dim destWB As Workbook
Dim refWB As Workbook
Dim newWB As Workbook
Dim mainFile As String, refFile As String
Dim totalRows As Integer, rowno As Integer

mainFile = ThisWorkbook.Path & "\Actuals_Country.xlsx"
refFile = ThisWorkbook.Path & "\List.xlsx"

'Open both files
Set mainWB = Workbooks.Open(mainFile)
Set refWB = Workbooks.Open(refFile)

Application.ScreenUpdating = False

'Find total rows available in the first column of reference file
totalRows = refWB.Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

'Loop to replace sheet name for each value
For rowno = 2 To totalRows
    mainWB.SaveCopyAs ThisWorkbook.Path & "\" & refWB.ActiveSheet.Range("A" & rowno) & ".xlsx"
    Set newWB = Workbooks.Open(ThisWorkbook.Path & "\" & refWB.ActiveSheet.Range("A" & rowno) & ".xlsx")
    newWB.Sheets("FY - COUNTRY").Name = "FY - " & _
    refWB.ActiveSheet.Range("A" & rowno)
    
    newWB.Save
    newWB.Close
Next
refWB.Close
mainWB.Close
Application.ScreenUpdating = True

End Sub
 
Upvote 0
Solution
Hi,

Check below code:

VBA Code:
Sub fileHandling()
Dim mainWB As Workbook
Dim destWB As Workbook
Dim refWB As Workbook
Dim newWB As Workbook
Dim mainFile As String, refFile As String
Dim totalRows As Integer, rowno As Integer

mainFile = ThisWorkbook.Path & "\Actuals_Country.xlsx"
refFile = ThisWorkbook.Path & "\List.xlsx"

'Open both files
Set mainWB = Workbooks.Open(mainFile)
Set refWB = Workbooks.Open(refFile)

Application.ScreenUpdating = False

'Find total rows available in the first column of reference file
totalRows = refWB.Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

'Loop to replace sheet name for each value
For rowno = 2 To totalRows
    mainWB.SaveCopyAs ThisWorkbook.Path & "\" & refWB.ActiveSheet.Range("A" & rowno) & ".xlsx"
    Set newWB = Workbooks.Open(ThisWorkbook.Path & "\" & refWB.ActiveSheet.Range("A" & rowno) & ".xlsx")
    newWB.Sheets("FY - COUNTRY").Name = "FY - " & _
    refWB.ActiveSheet.Range("A" & rowno)
   
    newWB.Save
    newWB.Close
Next
refWB.Close
mainWB.Close
Application.ScreenUpdating = True

End Sub
Thank you for your response! I tried it, but the main file closes and reopens without any changes.
 
Upvote 0
Hi, as per macro -

1. Both files (Actuals_Country.xlsx and List.xlsx) must be in same folder.
2. Are the above file names correct ?
3. What is the sheet name in List.xlsx. As per macro it is "Sheet1".
4. Before FOR loop type msgbox totalRows . After running macro what it's showing ?

Please check and confirm. The possibility is that the file name or sheet name is not matching.
 
Upvote 0
Hi, as per macro -

1. Both files (Actuals_Country.xlsx and List.xlsx) must be in same folder.
2. Are the above file names correct ?
3. What is the sheet name in List.xlsx. As per macro it is "Sheet1".
4. Before FOR loop type msgbox totalRows . After running macro what it's showing ?

Please check and confirm. The possibility is that the file name or sheet name is not matching.

Ah it works!!!!! I was running my macro within the main file, which was why it didn't work. This is MAGICAL.

I've updated the sheet names that need to be changed as below. There are now 2 sheets (not next to each other) that need "REGION" replaced with the list value. I tried doing the below, but it didn't work (The first sheet's name was replaced with "False"). How do I do multiple sheets?

VBA Code:
    newWB.Sheets("FY - TOTAL REGION").Name = "FY - TOTAL " & _
    newWB.Sheets("QTR - TOTAL REGION").Name = "QTR - TOTAL " & _
 
Upvote 0
Hi, as per macro -

1. Both files (Actuals_Country.xlsx and List.xlsx) must be in same folder.
2. Are the above file names correct ?
3. What is the sheet name in List.xlsx. As per macro it is "Sheet1".
4. Before FOR loop type msgbox totalRows . After running macro what it's showing ?

Please check and confirm. The possibility is that the file name or sheet name is not matching.
Nevermind regarding my prior message, I figured it out.

Thank you so, SO MUCH! This will save me a ridiculous amount of time. Even more inspired to properly learn macros.
 
Upvote 0
Great. Thanks for the details and feedback.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

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