need help revising a loop. Why cant i get it to paste?

[copleyr]

My old loop below copied rows #1-10 on my "reference sheet" tab and pasted it to a certain area on all of my MySheets tabs.


This is the code I had:

Sub CopyToAllSheets()
Dim ws As Worksheet
Dim MySheets
Dim LastRow As Long
Dim a As Long

Application.ScreenUpdating = False
MySheets = Array("FY09 Installation Support", "FY09 Install", "FY09 Purchase", "FY09 CF Discretionary Grants", "FY09 CF LOI", "FY08 Purchase", "FY08 Installation Support", "FY08 CF Discretionary Grants", "FY07 Sup Install Support", "FY07 CF Install Non-LOI", "FY07 Sup Purchase", "FY05 CF Carryover Install", "FY04 Recovery Funds", "FY05 Recovery Funds", "FY08 Safety Carryover", "FY09 Safety", "FY09 Transport Canada")

For a = LBound(MySheets) To UBound(MySheets)

Sheets("Reference Sheet").Rows("1:12").Copy

Set ws = Worksheets(MySheets(a))

With ws
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
.Cells(LastRow + 2, "A").PasteSpecial
End With
Next a

Application.ScreenUpdating = True


End Sub

Instead:

I needed it to copy rows #1-12 from the "reference sheet" tab and paste it in the same LastRow area on the first listed sheet, "FY09 Installation Support".

then

copy rows #12-24 from the "reference sheet" tab and paste it in the same area on the next listed sheet, "FY09 Install"

I need it to do this for all of the listed sheets in MySheets. the copy sequence in rows would therefore be:

#1-12, 12-24, 24-36, 36-48, ......, 192-204. (17 sheets)



This is the code I am using but I am having trouble pasting:

Sub CopyToAllSheets()
    Dim MySheets As Variant, sheetName As Variant
    Dim startRow As Long, endRow As Long
   
    Application.ScreenUpdating = False
   
    MySheets = Array("FY09 Installation Support", "FY09 Install", "FY09 Purchase", "FY09 CF Discretionary Grants", _
        "FY09 CF LOI", "FY08 Purchase", "FY08 Installation Support", "FY08 CF Discretionary Grants", _
        "FY07 Sup Install Support", "FY07 CF Install Non-LOI", "FY07 Sup Purchase", "FY05 CF Carryover Install", _
        "FY04 Recovery Funds", "FY05 Recovery Funds", "FY08 Safety Carryover", "FY09 Safety", "FY09 Transport Canada")
   
    startRow = 1: endRow = 12
    For Each sheetName In MySheets
        Sheets("Reference Sheet").Rows(startRow & ":" & endRow).Copy Sheets(sheetName).Rows(startRow)
        startRow = endRow
        endRow = endRow + 12
   
    Set ws = Worksheets(MySheets)
     With ws
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
.Cells(LastRow + 2, "A").PasteSpecial
End With
   
    Next
   
   
    Application.ScreenUpdating = True
End Sub

Any ideas on why it wont paste?


As always, thank you.

[protonLeah]

Original:

Set ws = Worksheets(MySheets(a))

new:

Set ws = Worksheets(MySheets)

where MySheets is an array of sheets

Can you paste into an entire array at one time? Is that what you are trying for?

also:

For Each sheetName In MySheets...

Therefore, should Set ws = Worksheets(MySheets) actually be:
Set ws = Worksheets(sheetName)?

[copleyr]

This is the code I used:

Sub CopyToAllSheets()
Dim MySheets As Variant, sheetName As Variant

Dim startRow As Long, endRow As Long, a As Long
Application.ScreenUpdating = False
MySheets = Array("FY09 Installation Support", "FY09 Install", "FY09 Purchase", "FY09 CF Discretionary Grants", _
"FY09 CF LOI", "FY08 Purchase", "FY08 Installation Support", "FY08 CF Discretionary Grants", _
"FY07 Sup Install Support", "FY07 CF Install Non-LOI", "FY07 Sup Purchase", "FY05 CF Carryover Install", _
"FY04 Recovery Funds", "FY05 Recovery Funds", "FY08 Safety Carryover", "FY09 Safety", "FY09 Transport Canada")
startRow = 1: endRow = 12
For Each sheetName In MySheets

Sheets("Reference Sheet").Rows(startRow & ":" & endRow).Copy Sheets(sheetName).Cells(Rows.Count, "A").End(xlUp).Offset(2).PasteSpecial
startRow = endRow
endRow = endRow + 12

Next

Application.ScreenUpdating = True
End Sub


I ran it and it came back with an error: Unable to get the PasteSpecial property of the Range class.

Any ideas?

Thanks again

[copleyr]

Nevermind I figured it out. It worked on this code:

Sub CopyToAllSheets()
Dim MySheets As Variant, sheetName As Variant
Dim startRow As Long, endRow As Long, a As Long

Application.ScreenUpdating = False

MySheets = Array("FY09 Installation Support", "FY09 Install", "FY09 Purchase", "FY09 CF Discretionary Grants", _
"FY09 CF LOI", "FY08 Purchase", "FY08 Installation Support", "FY08 CF Discretionary Grants", _
"FY07 Sup Install Support", "FY07 CF Install Non-LOI", "FY07 Sup Purchase", "FY05 CF Carryover Install", _
"FY04 Recovery Funds", "FY05 Recovery Funds", "FY08 Safety Carryover", "FY09 Safety", "FY09 Transport Canada")

startRow = 1: endRow = 12
For Each sheetName In MySheets
Sheets("Reference Sheet").Rows(startRow & ":" & endRow).Copy Sheets(sheetName).Cells(Rows.Count, "A").End(xlUp).Offset(2)
startRow = endRow
endRow = endRow + 12


Next



Application.ScreenUpdating = True
End Sub