Code Adjustment on Copied WorkSheet and Sheet Name Promptbox

[GeneBF]

Hi, I have a sheet with a VBA Code that has a code to activate different Sheet reference and a code coming back to original sheet.

Sample: the button containing this code is in "Sheet 1" , upon clicking it needs to go to 'sheet 2' to modify something then go back to 'sheet 1'

 ActiveWorkbook.Sheets("Sheet 2").Activate
    Sheets("Sheet 2").Range("A1").Select
      Selection.Copy
      Sheets("INPUT").Range("P7:P1000").Select
      Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
      Application.CutCopyMode = False
      
   Sheets("Sheet 1").Select
    Range("G4").Select

now sometimes i need to duplicate Sheet 1, to a new sheet within same workbook. The problem is the code reference that it will copy will remain on "sheet 1" instead of supposedly "sheet 1 (1)"
How to code it that it will comeback to the original activated worksheet?

and another one, is there a code where for example when clicking a button to copy the sheet it will prompt a box that can enter the name of the sheet, and referencing that input in the code aswell?

 

[Peter_SSs]

I don't think that is your actual code since it activates 'Sheet 2' then tries to select a range on 'INPUT' which causes an error as you can only select on the active sheet.

Never-the-less, see if a change like this resolves the question that you have asked.

Sheets("Sheet 1").Select
Me.Select

Having said that, it is rare to need to actually select anything in vba and doing so generally slows your code. If you would like to see a 'non-select' alternative to your code, please post the actual working code being used.

 

[GeneBF]

I don't think that is your actual code since it activates 'Sheet 2' then tries to select a range on 'INPUT' which causes an error as you can only select on the active sheet.

Never-the-less, see if a change like this resolves the question that you have asked.

Sheets("Sheet 1").Select
Me.Select

Having said that, it is rare to need to actually select anything in vba and doing so generally slows your code. If you would like to see a 'non-select' alternative to your code, please post the actual working code being used.

ah right the "input" is supposed to be sheet 2 as well. i forgot to replace it. it is actually really long so i just selected the part of the code and replace the sheet names.

i phrased the question that way so i can also apply the method to other sheet.

here is the part of the code that is currently working for me (may not be the most efficient way but it works it really needs to comeback at A1 of the sheet.)

'Spill Dimgrp Transpose Copy Fix
    ActiveWorkbook.Sheets("INPUT").Activate
    Sheets("INPUT").Range("P7").Select
    Selection.Copy
    Sheets("INPUT").Range("P7:P1000").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False     
        
    Sheets("COSTPLAN").Select
    Range("A1").Select

The case is I need to duplicate this "costplan" sheet and need the input to be duplicated as well then both the code referencing each sheet to adjust to the new named sheet, hence the promptbox question too.

so that the Costplan (2) VBA Code will contain bot sheets("COSTPLAN (2)").range etc. and sheets("INPUT (2)).range etc.

 

[Peter_SSs]

Does your code need to be in the COSTPLAN worksheet module?
Could you have this in a general module and call it from whatever COSTPLAN worksheet that you are on? This code never takes you away from whatever sheet you are on so it eliminates the need to "go back to the correct sheet".

Sub CopyINPUTFormula()
  Dim sName As String
  Dim wsInput As Worksheet
  
  sName = InputBox("Enter input sheet name that you want to use")
  On Error Resume Next
  Set wsInput = Sheets(sName)
  On Error GoTo 0
  If wsInput Is Nothing Then
    MsgBox "Nothing copied"
  Else
    wsInput.Range("P7").Copy
    wsInput.Range("P7:P1000").PasteSpecial Paste:=xlPasteFormulas
  End If
End Sub

 

[GeneBF]

Does your code need to be in the COSTPLAN worksheet module?
Could you have this in a general module and call it from whatever COSTPLAN worksheet that you are on? This code never takes you away from whatever sheet you are on so it eliminates the need to "go back to the correct sheet".

Sub CopyINPUTFormula()
  Dim sName As String
  Dim wsInput As Worksheet
 
  sName = InputBox("Enter input sheet name that you want to use")
  On Error Resume Next
  Set wsInput = Sheets(sName)
  On Error GoTo 0
  If wsInput Is Nothing Then
    MsgBox "Nothing copied"
  Else
    wsInput.Range("P7").Copy
    wsInput.Range("P7:P1000").PasteSpecial Paste:=xlPasteFormulas
  End If
End Sub

This is not what i was meaning to do,
not necessarily needed to be in worksheet, I can just assign it to a button in the worksheet.

here's a shorter code that have same case, you may ignore the first part and proceed to the last one

Private Sub CommandButton1_Click()

'Main Formula Copy Fix
    Range("F7").Select
    Selection.Copy
    Range("F7:F1000").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        
'Zone code combined Copy Fix
    Range("J7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("J7:J1000").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
 
    
'Zone Coding 1 to 8 Copy Fix
    Range("S7:Z7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select
    Range("S7:Z1000").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        
'Measurement Dropdown Copy Fix
    Range("G7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("G7:G1000").Select
    Selection.PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        
'DimGrp Dropdown Copy Fix
    Range("H7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("H7:L1000").Select
    Selection.PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        
'Custom Dropdown Copy Fix

    Range("I7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("I7:M1000").Select
    Selection.PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
   
        
'Spill Dimgrp Transpose Copy Fix
    ActiveWorkbook.Sheets("INPUT").Activate
    Sheets("INPUT").Range("P7").Select
    Selection.Copy
    Sheets("INPUT").Range("P7:P1000").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Sheets("INPUT").Range("A1").Select


' Go back to Main Sheet               
    Sheets("COSTPLAN").Select
    Range("G4").Select

    
End Sub

as you can see the last part is the same,
now what I wanted to do is code a button that will generate a duplicate of both Costplan and Input sheet, and have promptbox to name both of the new sheet, at the same time adjust the code of CommandButton1 to match those new sheet with basically the same code except the sheet reference (i'm having hard time phrasing what i want to code as im not proficient in coding, sorry) Hope we're both clear now sorry again

 

[GeneBF]

or atleast alternative modify the original code for commandbutton1 to have a prompt which =sheet it needs to 'Fix' being the first sheet is where the commandbutton1 is and the prompted one