VBA does not find my sheet name

Question

I have this piece of code in vba in Excel compared to the previous value from cliboard:

Dim WS as Worksheet
Set WS = Sheets.Add

SheetName= "New One"
Sheets("New One").Range("A11").PasteSpecial xlValues

And I have this problem error '9' Subscript out of range. If I change the name of the sheet to the technical name, how sheet19does it work well, but I don’t know the technical name of the sheet that is currently being created. Who can solve this? Thks in advance

Answer 1

Edit

SheetName= "New One"

AT

WS.Name= "New One"

Your expression does not change the name of the sheet, but simply assigns a variable. In addition, since you have a link on the sheet, that is WS, why call it again by name, why not just

WS.Range("A11").PasteSpecial xlPasteValues

Answer 2

Something like this works:

Public Sub TestMe()

    Dim WS          As Worksheet
    Dim SheetName   As String

    Set WS = Sheets.Add
    WS.Name = "New One"
    WS.Range("A11") = 45
    'Alternative:        
    Worksheets(WS.Name).Range("A15") = 46

End Sub

WS Worksheets(WS.Name). Sheets, Charts, .

Answer 3

use this

    ws.Name= "New One"
    ws.Range("A11").PasteSpecial xlValues

Answer 4

It seems to me that your range is incorrectly defined.

It should be something like this range (cells (x1, y1), cells (x2, y2))

Also, if this is a constructed worksheet at run time, you may need to use ThisWorkbook or ActiveSheet links.

Try changing your range in the format above and let me know.