Forum Discussion

Faraj_M's avatar
Faraj_M
Copper Contributor
Apr 15, 2020
Solved

Macro on non active sheet

Hello, 

I am very new to VBA coding and I am having some problems with a VBA code which I found online:

 

Sub Create()
Dim I As Long
Dim xNumber As Integer
Dim xName As String
Dim xActiveSheet As Worksheet
On Error Resume Next
Application.ScreenUpdating = False
Set xActiveSheet = ActiveSheet
xNumber = InputBox("Enter number of times to copy the current sheet")
For I = 1 To xNumber
xName = ActiveSheet.Name
xActiveSheet.COPY After:=ActiveWorkbook.Sheets(xName)
ActiveSheet.Name = I
Next
xActiveSheet.Activate
Application.ScreenUpdating = True
End Sub

 

 

This duplicates the sheet which is currently active, but i want this to be a button on a sheet, but duplicate a different sheet. The button will be placed on a sheet named INPUT-2 and I want it to perform the code to a sheet called copy.

 

Could some help me to what i should change, or even any way to optimise the code

 

Thanks

 

  • Faraj_M 

    Change the line in the middle to this, and it should work.

    Set xActiveSheet = Sheets("Copy")

    To put a button on the INPUT sheet, select it from the "toolbox" on the Developer ribbon and assign the macro to it.

     

1 Reply

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    Faraj_M 

    Change the line in the middle to this, and it should work.

    Set xActiveSheet = Sheets("Copy")

    To put a button on the INPUT sheet, select it from the "toolbox" on the Developer ribbon and assign the macro to it.

     

Resources