Pasting from protected worksheet 1

[burtech]

Hi. I've got a problem with a sub which copies from a protected worksheet in one workbook, into another worksheet in a different workbook. I open the protected workbook with:

Workbooks.Open "filename.xls", ReadOnly

I then call an 'Import' sub which strips out several worksheets individually and pastes into my workbook.

Sub Import(AFilePrefix As String, loopCount As Integer)
'
' Import Macro
'

Dim sheetname As String

sheetname = ActiveWorkbook.Name

Application.ScreenUpdating = False

For i = 1 To 7
    Windows(AFilePrefix & "Performance.xls").Activate
    Sheets(dyArray(i)).Select
    Range("A1:AE124").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(sheetname).Activate
    Sheets(dyArray(i)).Select
    Range("A1:AE124").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    For j = 1 To 124
        Range("AF" & j).Value = dyArray(i)
        Range("AG" & j).Value = j
        Range("AH" & j).Value = "X"
    Next j
    Range("A1").Select
Next i

Sheets("Tools").Select
Range("C" & loopCount).Value = "Imported"

Range("A1").Select

Application.ScreenUpdating = True

End Sub

It gets as far as the Selection.PasteSpecial line and gives me an 1004 error, "cell you are trying to change is protected and therefore read-only". The sheet I am pasting to is not protected at all, only the source is.

I'd also like to be able to copy the entire worksheet and not just a selection (i.e. A1:AE124). I did try this before but this also resulted in an error. Any ideas?

Steve

 

[simeybt]

The reason you are getting the 1004 error is that you are selecting the protected sheet again before you paste the information.

For i = 1 To 7
    Windows(AFilePrefix & "Performance.xls").Activate
    Sheets(dyArray(i)).Select
    Range("A1:AE124").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(sheetname).Activate
    Sheets(dyArray(i)).Select 'you are selecting the protect sheet here, take this line out
    Range("A1:AE124").Select' just select A1 
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, ' you are now trying to paste the information back into the same sheet

you can use this peice of code to copy a worksheet to another worksheet

Sheets(ProtectedSheetName).Copy Before:=Workbooks(NewWorkbookName ).Sheets(1)' make sure you have the protected workbook selected

hope this helps

Simon

 

[burtech]

Sorry, but I don't think I understand.

For i = 1 To 7
    Windows(AFilePrefix & "Performance.xls").Activate 'selected protected workbook
    Sheets(dyArray(i)).Select 'selected worksheet to copy from
    Range("A1:AE124").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(sheetname).Activate 'selected my workbook
    Sheets(dyArray(i)).Select     'selected worksheet to copy to - if I take this out, I'm not pointing at the right worksheet
    Range("A1:AE124").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,

Steve

 

[simeybt]

sorry,
I think it was me misunderstanding, have a look at this code. The only thing i can think is that you have the protected workbook selected when this macro is called.

Sub Import(AFilePrefix As String, loopCount As Integer)
'
' Import Macro
Dim sheetname As String
sheetname = ActiveWorkbook.Name 'make sure you have your unprotected workbook selected at this point
Application.ScreenUpdating = False

For i = 1 To 7
    Windows(AFilePrefix & "Performance.xls").Activate
    Sheets(dyArray(i)).Select
    range("A1:AE124").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(sheetname).Activate ' again ensure this is the unprotected sheet you are activating
    Sheets(dyArray(i)).Select
    range("A1").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    For j = 1 To 124
        range("AF" & j).Value = dyArray(i)
        range("AG" & j).Value = j
        range("AH" & j).Value = "X"
    Next j
    range("A1").Select
Next i

Sheets("Tools").Select
range("C" & loopCount).Value = "Imported"
range("A1").Select
Application.ScreenUpdating = True

End Sub