VBA Help - Copying Data to specific sheet

[quant1313]

Hello,

I am trying to copy a specific range of data from a sheet titled "Summary Template" to any worksheet that is not titled "Summary Template" or "Priority Admin" but I am continually getting an error message. Below is the code

Sub Populate()
Dim ws As Worksheet
Dim lockedsheet As Worksheet
lockedsheet = Array("Summary Template", "Priority Admin")

Dim template As Worksheets
Set template = Worksheets("Summary Template")

For Each ws In ThisWorkbook.Sheets
    If Not ws.Name = lockedsheet.Name Then
        template.Range("A1:Y83").Copy
        ws.Range("A2").PasteSpecial (xlPasteAll)
    End If
Next ws

End Sub

Please help!

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub Populate()
Dim ws As Worksheet
Dim template As Worksheets

Set template = Worksheets("Summary Template")

For Each ws In ThisWorkbook.Worksheets
   Select Case ws.Name
      Case "Summary Template", "Priority Admin"
      Case Else
         template.Range("A1:Y83").Copy
         ws.Range("A2").PasteSpecial (xlPasteAll)
   End Select
Next ws

End Sub

 

[quant1313]

the above worked

Hi & welcome to MrExcel.
How about

Sub Populate()
Dim ws As Worksheet
Dim template As Worksheets

Set template = Worksheets("Summary Template")

For Each ws In ThisWorkbook.Worksheets
   Select Case ws.Name
      Case "Summary Template", "Priority Admin"
      Case Else
         template.Range("A1:Y83").Copy
         ws.Range("A2").PasteSpecial (xlPasteAll)
   End Select
Next ws

End Sub

The above worked perfectly, thank you for the help. I have another question for you, if I wanted all sheets to be formatted the same (column width etc.) as the "Summary Template" except the Priority Admin sheet, do you know what the code for that would like ?

 

[Fluff]

As that is a different question, you will need to start a new thread.