CODEWITHSUNDEEP
vbaexcelindexing
12fretter
12fretter

Reputation: 79

using sheet index instead of name in VBA

I would like to copy some contents from my first sheet, paste them into a new sheet, then delete the first sheet and rename the new (only) sheet to Sheet1. But I never know what the second sheet will be named when I create it. Most of the time, it will be Sheet2, but I can't count on it. Here is the code taken just from creating a macro that does it, but it is using the sheet names as they were created in this instance. I want to use the index of the sheets instead but don't know the syntax:

    Columns("A:D").Select
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.Delete
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "Sheet1"

So where is says "Sheet2"...how do I make that so it's using the second sheet, not necessarily the sheet named Sheet2?

Thanks.

Upvotes: 3

Views: 22643

Answers (2)

Scott Craner
Scott Craner

Reputation: 152450

you can create a variable that will be the new sheet:

Sub sheetdelet()
Dim ws As Worksheet
With ThisWorkbook
    Set ws = .Worksheets.Add
    .Worksheets("Sheet1").Range("A:D").Copy ws.Range("A1")
    Application.DisplayAlerts = False
    .Worksheets("Sheet1").Delete
    Application.DisplayAlerts = True
    ws.Name = "Sheet1"
End With

End Sub

Upvotes: 2

Vityata
Vityata

Reputation: 43565

Worksheets(2) is the way to refer to the second sheet.

Worksheets(Worksheets.Count) to the last one.

Try it like this:

Debug.Print Worksheets(Worksheets.Count).name

Upvotes: 5

Related Questions