Excel VBA Create buttons to copy/rename sheets and whole workbook

Question

I am trying to create a roadway design spreadsheet that can be used over and over again for hundreds of alignments. I got the everything working with a master sheet to be copied and two sheets used as lookup tables. I want to add two buttons in the corner that can be used to:

1) Copy the Master Sheet and rename it the alignmentname-##. This will be within the current workbook and will be used for each curve in the roadway alignment. It would be even better if there was a way to delete out these two buttons in the copied sheets.

2) A button to copy just the Master sheet, and two supplement sheets to a new workbook.

So far I have:

Sub Button10_Click()
    Worksheets("Master (DO NOT MODIFY)").Copy _
    Before:=ActiveWorkbook.Sheets("Master (DO NOT MODIFY)")
End Sub

It works fine for now just creating a copy of the base file, but I have not been able to rename it. The code for renaming the sheet is not working and I am not quite sure why.

Sheets(Count).Name = Range("H7").Value & "-" & Count

Where count is a public variable that goes up by one everytime a new curve is addedand H7 is the name of the alignment.

I have also played with the ActiveSheet. activesheet and Worksheets

Code for the first Button:

Public Count As Integer
Sub Button10_Click()
If Count = 0 Then
    Count = 1
End If

Dim ws As Worksheet

Worksheets("Master (DO NOT MODIFY)").Copy _
Before:=ActiveWorkbook.Sheets("Master (DO NOT MODIFY)")

Set ws = ActiveSheet

ws.Name = Range("C2").Value & Count
Count = Count + 1
End Sub

Siddharth Rout · Accepted Answer · 2013-12-20 00:00:24Z

4

Is this what you are trying? You cannot use count as the sheet is moved before the sheet and if you are incrementing the count then it will refer to the wrong sheet.

Sub Button10_Click()
    Dim ws As Worksheet

    Worksheets("Master (DO NOT MODIFY)").Copy _
    Before:=ActiveWorkbook.Sheets("Master (DO NOT MODIFY)")

    Set ws = ActiveSheet

    ws.Name = Range("H7").Value & "-" & Count
End Sub

Regarding your 2nd question, you can try it like this. I am assuming that the master is not the last or the 2nd last sheet. Also there are two more sheets after the master.

Sub Button10_Click()
    Dim ws As Worksheet
    Dim wsMaster As Worksheet
    Dim MyArray(1 To 3) As String
    Dim n As Long

    Set wsMaster = ThisWorkbook.Worksheets("Master (DO NOT MODIFY)")

    wsMaster.Copy Before:=wsMaster

    Set ws = ActiveSheet

    ws.Name = Range("H7").Value & "-" & Count

    n = ThisWorkbook.Sheets.Count

    MyArray(1) = wsMaster.Name
    MyArray(2) = ThisWorkbook.Sheets(n - 1).Name
    MyArray(3) = ThisWorkbook.Sheets(n).Name

    '~~> This will create a new workbook with the 3 sheets
    ThisWorkbook.Sheets(MyArray).Copy
End Sub

edited Dec 20, 2013 at 0:00

answered Dec 19, 2013 at 23:39

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

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

2 Comments

Matt Over a year ago

Thanks for that. I didn't think of defining the active sheet like that. I appended the original post with the Code for the first button. Do you know how I can have it copy the last three sheets to a new workbook?

Siddharth Rout Over a year ago

One moment. Updating the answer

Collectives™ on Stack Overflow

Excel VBA Create buttons to copy/rename sheets and whole workbook

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related