Specify name of sheet in VBA-CodePudding

I have this vba code that puts sheet1 of all workbooks in a folder in as sheets in one workbook. This works all fine.

What I want to do is change the name of each sheet that is copied in to my workbook. Then I want to overwrite the files that already exists in the workbook.

Hope someone can help me with a solution.

Sub CombineFilesInSheets()
     
    Dim Path            As String
    Dim FileName        As String
    Dim Wkb             As Workbook
    Dim WS              As Worksheet
     
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Path = "*The path*" 'Change as needed
    FileName = Dir(Path & "\*.xls", vbNormal)
    Do Until FileName = ""
        Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName)
        Worksheets("Sheet1").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)    
        Wkb.Close False
        FileName = Dir()
    Loop
    Application.EnableEvents = True
    Application.ScreenUpdating = True
     
End Sub

CodePudding user response：

There are a couple ways to go about your request, and not to steal from /u/VBasic2008, but he's on a similar line of thinking to me.

'open workbook like you do
Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName)
'perform your regular copy
Worksheets("Sheet1").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
'define a name
dim desiredSheetName as string
desiredSheetName = Wkb.Name 'takes the workbook name
'check if the desired name exists, and if so, delete the old sheet
If Not IsError(Evaluate(desiredSheetName & "!A1")) Then ThisWorkbook.Sheets(desiredSheetName).Delete
'name the last added sheet in ThisWorkbook the desired name
ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name = desiredSheetName

You could similarly use the check to do nothing if the desiredSheetName already exists, though I believe the above fits your post.

CodePudding user response：

I fixed this by running a macro afterwards to delete and edit names of sheets