Unprotect/Write to 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. No real previous VBA experience.

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

 

[VBAjedi]

Two things to try:

First, try unprotecting your destination sheet first (before you copy the source data). Some operations can clear the clipboard, and this might (can't remember for sure) be one of them.

Second, you shouldn't need to select a range on your destination sheet. Selecting ranges is almost ALWAYS unnecessary (the macro recorder uses that so it can track what the user is doing, but it's usually best to rewrite that part of the code). In your case:

    With Worksheets("TubingHist")
        .Range("A1").Insert Shift:=xlDown
    End With

should do the trick. For an Insert statement, you only have to designate a single cell - the cell you want the clipboard contents to be inserted before. In your case, it looks like you want it at the top of the sheet (before cell A1).

Let me know if that solves it!



VBAjedi

 

[analogkid2digitalman]

VBAjedi:

CHING!

You nailed it on the head with your first suggestion.
Moving the Open/Activate/Unprotect prior to copy worked.
And kept an activate to the destination prior to paste.

Thanks, I spent a couple hours trying things before posting, but missed this.

-Pete

 

[VBAjedi]

Glad I could help! I learned the hard way that it's usually best to keep paste statements as close as possible to the copy statement.

VBAjedi