Unprotect another Workbook via Macro

[analogkid2digitalman]

Hello:

I am writing a macro to insert data from one workbook (source) into another workbook comprised of one sheet (target)that is protected.

In code snippet below, I activate my source worksheet, define my copy range (1st With/End With block) and copy it.

Then I open my protected target workbook, activate it, try to unprotect it. Then the 2nd With/End With block inserts and pastes the lines of data into the target. Then reprotect and save and close the target workbook.

Data does not get pasted to target, just rows of blank cells.

The code, as shown with the Protect and Unprotect lines commented out, works perfectly if the taget workbook is unptotected.

Any ideas?

Thanks

-Pete

' Compile a range of output for valid entries, then write data to archive
' Need at least 1 valid entry so that ranges below are valid thus check if b at least zed
If b >= 0 Then

Workbooks("TubingCost.xls&quot.Activate

With Worksheets("Summary&quot
.Range(.Cells(a, 1), .Cells(a + b, c)).Select
Selection.Copy
End With

Workbooks.Open DatabasePath
Workbooks("TubingHistory.xls&quot.Activate
' Worksheets("TubingHist&quot.Unprotect BUG- ONLY WRITES LINES OF BLANK DATA

With Worksheets("TubingHist&quot
.Range(.Cells(a1, 1), .Cells(a1 + b, c)).Select
Selection.Insert Shift:=xlDown
End With

' Worksheets("TubingHist&quot.Protect BUG- ONLY WRITES BLANK DATA

Workbooks("TubingHistory.xls&quot.Close SaveChanges:=True

Workbooks("TubingCost.xls&quot.Activate
Worksheets("Summary&quot.Activate

End If

 

[joerd]

You get blanks because Excel "forgets" what you copied when you don't immediately paste the data. So the easiest for you is to open and unprotect the target workbook first, and then do the copy and paste operation:

    Workbooks.Open DatabasePath
    Worksheets("TubingHist").Unprotect
  
    Workbooks("TubingCost.xls").Activate
    With Worksheets("Summary")
        .Range(.Cells(a, 1), .Cells(a + b, c)).Copy
    End With
    
    Workbooks("TubingHistory.xls").Activate
    With Worksheets("TubingHist")
        .Range(.Cells(a1, 1), .Cells(a1 + b, c)).Insert Shift:=xlDown
    End With
    
    Worksheets("TubingHist").Protect
    
    Workbooks("TubingHistory.xls").Close SaveChanges:=True
    
    Workbooks("TubingCost.xls").Activate
    Worksheets("Summary").Activate

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[analogkid2digitalman]

Joerd:

Thanks for the tip. Yes, I have the code working now, odd that Excel forgets, it was a behaviour I did not expect.

Thanks

-Pete

 

[sandgnatce]

Pardon my rookie question, nut can this code be modified to unprotect a password protected worksheet and reprotect with same password?

Thanks,
Jeff

 

[analogkid2digitalman]

Jeff:

Funny you should ask. In my same macro, I later chose to alter a worksheet that was protected, and then reprotect after I was done with the modifications. But you must know the password that the sheet was originally protected with (ie MyPassword below)

Worksheets("Summary&quot.Unprotect Password:="MyPassword"
' Do operations needed here
Worksheets("Summary&quot.Protect Password:="MyPassword"

Hope this helps, from one rookie to another.

-Pete

 

[sandgnatce]

Pete,
Thanks. This looks like it will solve my problem! This forum is incredibly useful, don't you think!

Thanks again,
Jeff

 

[joerd]

Just a tip:
The statement workbook.protect password:="whatever" reveals the protection password to whomever cares to look in the VBA code. Unless you don't care, but then why use a password at all , you should also protect your VBA project with a password (Tools/VBA Project Properties/Protection tab, from the VB editor).

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.