Access a work book and create new sheet with name value sets

Question

I want to open an existing excel work book and add a sheet with given name.. All this has to be done from a different excel work book

I am able to open the existing work book from a blank work book by executing code. But when I tired to add a new sheet to the opened work book, my code is adding the new sheet in the execution work book (Blank work book)

Sub Valuesets()
Dim ws As Worksheets
Dim PTable1 As PivotTable
Dim sheet As Sheets
Set wb = ActiveWorkbook

Workbooks.Open("C:\Users\user\Desktop\workbook1.xlsb").Worksheets("Data").Activate
On Error Resume Next
Application.DisplayAlerts = False
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "Test-1"
Application.DisplayAlerts = True
Set CSheet = Worksheets("Data")
Set SSheet = Worksheets("Test-1")
End Sub

Answer 1

You should set your opened workbook into a variable OpenWb so you can access it to add a sheet in this specific workbook OpenWb.Sheets.Add. The same with the data sheet and the new sheet. Always avoid using .Activate, ActiveSheet and .Select like described in How to avoid using Select in Excel VBA.

Also make sure you don't use On Error Resume Next as you did. This line hides all error messages until End Sub or On Error Goto 0 but the errors still occur. You just hide their messages. If you don't see the errors you cannot fix them. If you don't tell the user that something went wrong, he will continue with that wrong status!

Always implement a proper error handling: VBA Error Handling – A Complete Guide.
Using On Error Resume Next (unless you know in which rare cases this can be used) is a very bad practice and will produce more issues instead of preventing issues.

Sub Valuesets()
    Dim OpenWb As Workbook
    Set OpenWb = Workbooks.Open("C:\Users\user\Desktop\workbook1.xlsb")

    Dim wsData As Worksheet
    Set wsData = OpenWb.Worksheets("Data")

    Application.DisplayAlerts = False

    Dim NewSheet As Worksheet
    Set NewSheet = OpenWb.Sheets.Add(Before:=wsData)
    NewSheet.Name = "Test-1"

    Application.DisplayAlerts = True
End Sub