Simple VBA code to move, copy, paste, and protect

[clifton1230]

I'm new to visual basic so I would assume that this is fairly simple.

I need a macro that will unlock my current workbook (just the workbook, not the sheet), then move, copy, and paste values (only) the first two tabs to a new workbook. Then I need it to go back to the original workbook and re-lock it.

I was able to get the unprotect, move and copy to new sheet (still need the paste values bit), but when I use the ActiveWorkbook.protect function, it locks the new workbook. I need to new workbook unproteted and the old one to lock back.

I'm assuming I need to reference the file name in order to re-lock the workbook. How can I do that if my file name changes weekly?

ActiveWorkbook.Unprotect "password"
    Sheets("#1 Weekly Report").Select
    Sheets("#1 Weekly Report").Copy
    ActiveWorkbook.Protect "password", Structure:=True, Windows:=False

Thank you -

Cliff

[arlu1201]

Do you open the file through the macro? The file that needs to be protected again?

[clifton1230]

So while looking at this I realized that the workbook doesn't have to be unlocked to copy and paste the values. Eventually I will lock it down so they cannot see locked cells, but that will be later.

When recording the macro here's the code that generates:

Sub Copy2()
'
' Copy2 Macro
' Copy and paste values into new workbook
'

'
    Workbooks.Add
    Windows("Copy of WR_CPF_3-10-2013.xlsm").Activate
    Cells.Select
    Selection.Copy
    Windows("Book5").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("A6:K6").Select
    Windows("Copy of WR_CPF_3-10-2013.xlsm").Activate
    Sheets("#2 Market Survey").Select
    Cells.Select
    Range("A2").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Book5").Activate
    Sheets("Sheet2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Windows("Copy of WR_CPF_3-10-2013.xlsm").Activate
    Sheets("Comp Sheet").Select
    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Book5").Activate
    Sheets("Sheet3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
End Sub

The problem is it is referencing a specific workbook ("Copy of WR_CPF_3-10-2013.xlsm" and "Book5.xlsx"). This is a weekly report so the file name changes week to week. I need it to recognize the current file name and use that as the "active workbook" and I need to lose the "Book 5" name because it will not always be "Book 5."

Does all this make sense?

[arlu1201]

So is this workbook Copy of WR_CPF_3-10-2013.xlsm already open at the time of running the macro?

Is this macro in this workbook or in another workbook?

[clifton1230]

The macro will be built into the "Copy of WR_CPF_3-10-2013.xlsm" workbook. But that file extension will change week to week. For example, when our employees update this workbook next week, they will save it as "WR_CPF_3-17-2013.xlsm." So I need the macro to reference the active workbook file extension each time it is run.

[arlu1201]

In that case, it will be better if you keep the code in one central workbook and then have the workbook open the file required.

This way, if you need to make changes to the macro in the future, you do not have to edit the codes in all the files.