CODEWITHSUNDEEP
vbaexcel
bacotico
bacotico

Reputation: 17

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

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

Upvotes: 1

Views: 646

Answers (1)

KacireeSoftware
KacireeSoftware

Reputation: 808

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")

Upvotes: 1

Related Questions