[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

[Siddharth Rout]

Try this...

Instead of

Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy

replace it with

Range(Range("A2"), Range("A2").SpecialCells(xlLastCell)).Copy

[Cristobal16]

No, that didn't work, it still gave me the error:

"Run-time error '1004':
You cannot use this command on a protected sheet. TO use this command, you must first unprotect the sheet (Review tab, Changes group, Unprotect Sheet button). You may be prompted for a password."

Also, when the MsgBox prompts user to choose if they wish to include the worksheet and the user selects No, instead of continuing on to the next worksheet the process just ends... Not sure why.

[Cristobal16]

All right, I fixed the continuation prompt issue, so the process now works smoothly when asking if you want to include the worksheet and move on to the next, but I'm still getting this:

"Run-time error '1004':
You cannot use this command on a protected sheet. To use this command, you must first unprotect the sheet (Review tab, Changes group, Unprotect Sheet button). You may be prompted for a password."

When I choose Debug, it sends me straight to:

Range(Range("A2"), Range("A2").SpecialCells(xlLastCell)).Copy

Again, is there any way that I can do this (or something equivalent) without unprotecting the sheet? I can do it by hand, and when I record the macro by hand, this is the code that it gives me:

Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy

Unfortunately, this causes the same error as what Koolsid suggested. Any help is greatly appreciated. Thanks!

[Siddharth Rout]

If you would have known the password then it would have been easy...

anyways try this....

Replace

Range(Range("A2"), Range("A2").SpecialCells(xlLastCell)).Copy

by

    Range(Range(Range("A2"), Range("A2").End(xlToRight)), Range(Range("A2"), _
    Range("A2").End(xlToRight)).End(xlDown)).Copy

Now give it a try...

[Cristobal16]

Yeah, I know, that's what I told my boss. Anyway, that worked great! Thanks!!!