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

[benp106]

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]

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]

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

 

[HaHoBe]

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]

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]

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

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