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
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