# Using VBA to make a copy of active sheet and go back to original Workbook.



## benp106 (Dec 27, 2022)

I'm having trouble with copying an active sheet to a new workbook and the going back to the original workbook.

This is what needs to happen:
-Current active sheet (source Workbook) - Select
-Current active sheet (source Workbook) - Unprotect
-Current active sheet (source Workbook) - Copy to new Workbook
-New workbook - Select all cells
-New workbook - Paste Values
- Go back to Source Workbook - Protect

This is my current code.  It works as long as the file name stays the same.  We will be changing the file name constantly.
I basically need to assign each sheet a name instead of just "active sheet".
I appreciate any help, Thanks!

Sub MAC()
'
' MAC Macro
' Make a Copy
'

'
    ActiveSheet.Select
    ActiveSheet.Unprotect
    ActiveSheet.Select
    ActiveSheet.Copy
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("W4").Select
    Application.CutCopyMode = False
    ActiveSheet.Shapes.Range(Array("TextBox 2")).Select
    Selection.Delete
    Windows("Contractor Production Sheet - Master - 2.0.xlsm").Activate
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub


----------



## HaHoBe (Dec 27, 2022)

Hi benp106,

maybe shorten the code to


```
Sub MAC_mod()
'
' MAC Macro
' Make a Copy
'

'
With ActiveSheet
  .Unprotect
  .Copy
End With

With ActiveWorkbook.ActiveSheet
  With .UsedRange
    .Value = .Value
  End With
  .Shapes.Range(Array("TextBox 2")).Delete
  .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End With

With ThisWorkbook.ActiveSheet
  .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
  Application.Goto .Range("W4")
End With

End Sub
```

I would pass the name of the sheet as a parameter to the procedure if needed.

Ciao,
Holger


----------



## benp106 (Dec 27, 2022)

Thanks for the help, Holger.  Seems to work great!


----------



## HaHoBe (Dec 27, 2022)

Hi benp106,

is it only one sheet you want to copy or several? If several: will the name for the TextBox always be the same or are they different? If so the hard-coded shape name should be substituted by a variable like


```
.Shapes(1).Delete
```

if there is only one shape on the sheet.

Holger


----------



## smozgur (Dec 28, 2022)

benp106 said:


> Thanks for the help, Holger.  Seems to work great!


The marked solution post has been switched accordingly.

@benp106: Welcome to the MrExcel Message Board! 
In your future questions, that would be great if you could mark the post as the solution that answered your question to help future readers. There is no further action is required in this thread.


----------



## benp106 (Dec 31, 2022)

HaHoBe said:


> Hi benp106,
> 
> is it only one sheet you want to copy or several? If several: will the name for the TextBox always be the same or are they different? If so the hard-coded shape name should be substituted by a variable like
> 
> ...


Thanks, I’ve been working on many workbooks and this will definitely help. Thanks again.


----------

