[RESOLVED] [EXCEL] Import Sheets from Protected Workbook into New Workbook, Single Sheet

[Cristobal16]

Basically, I have 52 weeks' worth of data in one workbook, each week in a different worksheet. Each worksheet consists of the same number of columns. Each worksheet has the exact same header row, which is not to be included. I want to take the data from each worksheet and place it in a separate workbook.

However, the original workbook containing the 52 sheets is protected. I am able to manually select, copy and paste cells into the new workbook without a problem. When I run my script (see below), it gives me an error about how I cannot do the

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

without first removing protection from the worksheet. My boss gave me the worksheet, but doesn't remember/know the password.

I wrote the following to loop through all of the worksheets in the workbook. Each time it starts the loop on a worksheet, it msgbox asks if you want to include the current worksheet. If you say no, it goes to the next worksheet. If you say yes, it selects the desired range (A2 to the lower-right cell), copies, and pastes into the other workbook. Arkansas2009.xls is the original, multi-sheet workbook and Arkansas2009_compiled.xls is the destination workbook.

Any help on how to work around this error would be greatly appreciated. Here's the code:

Sub GetValues()
'
' GetValues Macro
'

'
   
   
   Windows("Arkansas2009.xls").Activate
For Each ws In Worksheets
   Worksheets(ws.Name).Activate
   Dim Msg1, Style1, Title1, Response1, Compiled As Integer, Continue As Integer
   Msg1 = "Do you wish to include this worksheet?"
   Style1 = vbYesNo
   Title1 = "Compilation confirmation"
   Response1 = MsgBox(Msg1, Style1, Title1)
   If Response1 = vbYes Then
        Range("A2").Select
        Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
        'Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
        Selection.Copy
        Windows("Arkansas2009_compiled.xls").Activate
        ActiveCell.SpecialCells(xlLastCell).Select
        Selection.End(xlToLeft).Select
        ActiveSheet.Paste
        Compiled = 1
    
    Else
        Compiled = 0
    End If
    
    'MsgBox ws.Name
    
    If Compiled = 1 Then
        Dim Msg, Style, Title, Response
        Msg = "Worksheet has been compiled. Do you wish to continue?"
        Style = vbYesNo
        Title = "Worksheet Compilation"
        Response = MsgBox(Msg, Style, Title)
        If Response = vbYes Then
            Continue = 1
        ElseIf Compiled = 0 Then
        Dim Msg2, Style2, Title2, Response2
        Msg2 = "Okay, would you like to try the next worksheet?"
        Style2 = vbYesNo
        Title2 = "Continue on or not?"
        Response2 = MsgBox(Msg2, Style2, Title2)
        If Response2 = vbYes Then
            Continue = 1
            Else
            Continue = 0
        End If
            
        End If
    Else
    
    End If
    
    If Continue = 1 Then
    Else
    Exit For
    End If
Next ws

End Sub