Forum Discussion
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
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_EekelenPlatinum Contributor
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.