using sheet index instead of name in VBA

Question

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.

Vityata · Accepted Answer · 2017-10-26 14:25:52Z

5

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

answered Oct 26, 2017 at 14:25

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

12fretter Over a year ago

So in the above example, replace all "Sheet2" with Worksheets(2)?

Vityata Over a year ago

@12fretter - yes

12fretter Over a year ago

I'm back with a renewed question, same topic. I have a new workbook, with this in one cell: ='[Daily Customer Count.xlsx]Mar 18'!$G$3 Where Mar 18 is the name of the sheet in another file. I want to always use sheet 1 of that other file instead of the one that is name Mar 18. I tried using Worksheets(1) but it returns an error. Can this be done without coding? Because I have a boatload of these to do once, then hopefully my new workbook will be auto updated every time someone enters a new sheet in the reference workbook.

Vityata Over a year ago

@12fretter - it would be really a good idea to ask the renewed question separately. Thus, more people would be able to answer :)

Scott Craner · Accepted Answer · 2017-10-26 14:27:37Z

2

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

answered Oct 26, 2017 at 14:27

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Collectives™ on Stack Overflow

using sheet index instead of name in VBA

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related