Name a copy of a Sheet the value in a cell in original sheet

Question

This script loops through a file pathway and combines all workbooks into a master workbook and sections each workbook into a worksheet within the master file.

I have a line ws.Name = activeworksheet.Range("B1").Value that names the sheets as a copy of the first workbook's worksheet Sheet1 and names every sheet after that like Sheet1(2).

How can this be changed to the value in cell B1 of the workbook it is consolidating into the master workbook?

rest of script:

Option Explicit

Sub CombineWorkbooks()

    Dim MainWB As Workbook
    Dim sDirPath As String
    Dim sFileName As String
    Dim sFilePath As String
    Dim wb As Workbook
    Dim ws As Worksheet

    sDirPath = "M:\New folder"

    sFileName = Dir(sDirPath & "\*.xlsx")

    Set MainWB = ThisWorkbook

    Do While Len(sFileName) > 0

        sFilePath = sDirPath & "\" & sFileName

        Set wb = Workbooks.Open(Filename:=sFilePath)

        For Each ws In wb.Sheets
            ws.Copy After:=MainWB.Sheets(MainWB.Sheets.Count)    
            ws.Name = activeworksheet.Range("B1").Value
        Next ws

        wb.Close SaveChanges:=False

        sFileName = Dir
    Loop
End Sub

@DisplayName it still keeps naming them as a copy of sheet1. Could it be the For Each loop where it's ws.copy after:=mainwb.sheets? — nick lanta
which cell in which worksheet of which workbook you want to name new worksheets after? — DisplayName
whatever sheet I grab from the folder. So I go in, grab the first workbook (sheet1) in that pathway, and whatever the value is in cell B1 of that workbookwhat I want to name the worksheet as when it is moved to the MainWb — nick lanta

DisplayName DisplayName · Accepted Answer · 2018-12-26T17:07:17

after .Copy() method of Sheet object, the newly created sheet becomes the active one, while ws still holds reference to the original sheet

so go like follows

        For Each ws In wb.Sheets
            ws.Copy After:=MainWB.Sheets(MainWB.Sheets.Count)

            ActiveSheet.Name = ws.Range("B1").Value
        Next

Name a copy of a Sheet the value in a cell in original sheet

1 Answers