CODEWITHSUNDEEP
vbaexcelchartsuserform
user3794203
user3794203

Reputation: 235

Referencing Most Recently Added Worksheet

I have a userform that fields the user's input to take certain actions within a workbook, one of the actions is inserting a new tab in the workbook and having the user input the new sheet's name within an input box. I want to be able to then reference this new sheet (but I won't know what someone else might name it) and to paste a chart object within the newly created sheet.

So far the adding sheet code is working fine, but any of my attempts to paste the chart range are not working. My current code for adding the worksheet is:

Private Sub MyChart_Click()

Dim Answer As String
Dim sht_name As Variant

On Error Resume Next

If Me.OptionButton2.Value = True Then

Unload Me

sht_name = InputBox("Please enter value")

If sht_name <> "" Then

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = sht_name

Else

Exit Sub

End Sub

My chart lives in another worksheet ("Sheet2") and I am trying to just copy it into the newly created sheet whenever the user selects this OptionButton2 in the Userform... Any help is appreciated.

Upvotes: 2

Views: 3628

Answers (1)

Soulfire
Soulfire

Reputation: 4296

When you use the Worksheets.Add method, that sheet automatically is activated. To test this you can run this small portion of code:

Option Explicit

Private Sub SheetReference()

        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Test"
        Debug.Print ActiveSheet.Name

End Sub

And the output you would see is

Test

So in your case, you could declare a worksheet variable and then set the reference after you call the add method. Something like this:

Option Explicit

Private Sub MyChart_Click()

        Dim Answer As String
        Dim sht_name As Variant
        Dim ws As Worksheet

        On Error Resume Next

        If Me.OptionButton2.Value = True Then

        Unload Me

        sht_name = InputBox("Please enter value")

        If sht_name <> "" Then

                Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = sht_name
                Set ws = ActiveSheet

                With ws
                        'Do whatever you need to do on the worksheet
                End With

        Else

                Exit Sub
        End If

End Sub

Upvotes: 3

Related Questions