Copy worksheet to new workbook and change the name of the worksheet to a compose name

Question

I'm copying a lot of workbooks to 1 workbook, but since all the worksheets on the different workbook have the same name, and each workbook have the name of the file on B1, so I want to name the new sheet as "violation(name)"

These is the code I have for that part

Dim wc As Worksheet
Dim ws As Worksheet
Dim SheetNewName As String
Dim Parname As String
Dim SheetName As String

Select Case wc.Cells(n, 1)
    Case "PK"
        SheetName = "Peak"
    Case "Sfactor"
        SheetName = "SF Supply"
    Case Else
        SheetName = wc.Cells(n, 1)
End Select

Set ws = ThisWorkbook.Sheets("Sheet1")
Set wc = ActiveWorkbook.Sheets("Violations Summary")
Set Parname = wc.Range("B1")
Set SheetNewName = SheetName & "(" & Parname & ")"

ActiveWorkbook.Sheets(SheetName).Copy After:=ThisWorkbook.Sheets(1)
Set lastsheet = ThisWorkbook.Sheets(ThisWorkbook.Sheets.count).Select
lastsheet.Name = SheetNewName

I get the an error when trying to change the name of the new sheet on the new workbook, error is

Run-time error '13': Type mismatch

On these line

Set SheetNewName = SheetName & "(" & Parname & ")"

But if I modify the code to be like these

Set Parname = wc.Range("B1")
Set SheetNewName = SheetName & "(" & Parname & ")"

ActiveWorkbook.Sheets(SheetName).Copy After:=ThisWorkbook.Sheets(1)
Set lastsheet = ThisWorkbook.Sheets(1).Select
lastsheet.Name = Parname

It copy the sheet, but dosen't change the name and get error

Run time error '1004': Application-defined or object-defined error

Is there a question being asked? SheetName doesn't exist so you can't set SheetNewName to it &.... unless it appears somewhere else in the code before? — QHarr
yes sheetname exist, idint put the code for it Select Case wc.Cells(n, 1) Case "PK" SheetName = "Peak" Case "Sfactor" SheetName = "SF Supply" Case Else SheetName = wc.Cells(n, 1) End Select — bacotico
Are you receiving an error? This code looks similar to previous post where the solution involved using the Worksheets collection not sheets — QHarr
Yes is the same code, but now i get the an error when trying to change the name of the new sheet on the new workbook, error is Run-time error '13': Type mismatch — bacotico

KacireeSoftware KacireeSoftware · Accepted Answer · 2017-10-17T22:36:59

You cannot "SET" as string variable:

Set SheetNewName = SheetName & "(" & Parname & ")"

Needs to be modified to

SheetNewName = SheetName & "(" & Parname & ")"

and

Set Parname = wc.Range("B1")

needs to be

Parname = wc.Range("B1")

Copy worksheet to new workbook and change the name of the worksheet to a compose name

1 Answers