VBA range

[dpaton05]

I have a button on my spreadsheet that copies a worksheet and pastes it in a new workbook. This is my code:

Private Sub cmdCopySheet_Click()
Worksheets("home").Unprotect Password:="costings"
    Dim CopyMonth As String
        CopyMonth = Range("I13").Value
        
        Worksheets(CopyMonth).Activate
        Worksheets(CopyMonth).Columns("A:E").copy
        'Worksheets("home").Activate
        Workbooks.Add
        ActiveSheet.Paste
Worksheets("home").Protect Password:="costings"
End Sub

It was working before I added in the password protection. However, once I added it in, I started having all these problems. Is there a better way to do it?

I13 =CONCATENATE(F13," ",H13)
F13: has the month
H13: has the year

The sheet names are the format "mmmm yyyy"

I get the error subscript out of range and the protect line of code (the last line) is highlighted.

 

[dpaton05]

I comment the line out,
Worksheets("home").Protect Password:="costings" and it works fine.

 

[Swayzy]

I would suspect it errors out because at that point there is two sheets named home and the code dosent know which is which because you havent declared parent workbook.

 

[Fluff]

The "New" workbook will be the active workbook when you try to protect the sheet & as that doesn't have a sheet called "Home" you get the error. Try

ThisWorkbook.Worksheets("home").Protect Password:="costings"

 

[dpaton05]

The "New" workbook will be the active workbook when you try to protect the sheet & as that doesn't have a sheet called "Home" you get the error. Try

ThisWorkbook.Worksheets("home").Protect Password:="costings"

I tried this and this is the code I have now.

Private Sub cmdCopySheet_Click()
Worksheets("home").Unprotect Password:="costings"
    Dim CopyMonth As String
        CopyMonth = Range("I13").Value
        
        Worksheets(CopyMonth).Activate
        Worksheets(CopyMonth).Columns("A:E").copy
        ThisWorkbook.Worksheets("home").Protect Password:="costings"
        'Worksheets("home").Activate
        Workbooks.Add
        ActiveSheet.Paste

End Sub

With that code I still get the error "paste method of worksheet class failed" and "ActiveSheet.Paste" is highlighted if I press debug.

 

[dpaton05]

I googled it and found this code

    Dim xWs As Worksheet
    Dim Rng As Range
    Set Rng = Application.Selection
    Application.Workbooks.Add
    Set xWs = Application.ActiveSheet
    Rng.Copy Destination: = xWs.Range("A1")

How do I incorporate it into my current code?

 

[Fluff]

Try

Private Sub cmdCopySheet_Click()
   Dim CopyMonth As String
   Worksheets("home").Unprotect Password:="costings"
   CopyMonth = Worksheets("home").Range("I13").Value
   Workbooks.Add
   With ThisWorkbook.Worksheets(CopyMonth)
      .Columns("A:E").Copy Range("A1")
   End With
   ThisWorkbook.Worksheets("home").Protect Password:="costings"
End Sub