WorkSheets in an Array - Copy From

[dj44]

Good Morning,

I am trying to set my worksheets in an array.


i did the below - but i get a type mismatch




Sub Copy_Ranges()

Dim i As Long
Dim s As String

Dim oCopyRange As Variant

Dim oSrcSht As Worksheet
Dim oDestSht As Worksheet




Set oDestSht = ThisWorkbook.Worksheets("AA") ' Paste to


oSrcSht = ThisWorkbook.Sheets(Array("Sheet1", "Sheet2")) ' Copy From

oCopyRange = Array("A1:A10", "B1:B10") ' Ranges to Copy

oDestinationRange = Array("A1", "A50") ' Destination Range



For i = LBound(oCopyRange) To UBound(oCopyRange)
oSrcSht.Range(oCopyRange(i)).Copy Destination:=oDestSht.Range(oDestinationRange(i))
Next


End Sub



may some one kindly spot my error

[mana]

With ThisWorkbook
oSrcSht = Array(.Sheets("Sheet1"), .Sheets("Sheet2")) ' Copy From
End With




oSrcSht(i).Range(oCopyRange(i)).Copy Destination:=oDestSht.Range(oDestinationRange(i))

[dj44]

Thank you Mana,


That did the trick :)

I also forgot to loop through the worksheets :doh:


oSrcSht(i).Range(oCopyRange(i)).Copy Destination:=oDestSht.Range(oDestinationRange(i))


But that seems solved

Have a great day!