Copy Array in 1 Workbook Paste Array in Another with LastRow

[mrmmickle1]

Not sure how to go about this process. I was hoping that someone could point me in the right direction. I need to copy and array of cells in one workbook and paste that array into another array of cells in the second workbook.

Copy WorkBook is "Example"
Paste Workbook is "Work"

Sub CopyPasteBetweenWB()
Dim Finalrow As Long
Dim i           As Integer

finalRow = ActiveSheet.UsedRange.Rows.count + 1
                                                       
CopyArray = Array( "I4", "C28", "K22", "M23", "F27", "M22", "K23")
PasteArray = Array(2,3,4,5,6,7,8)  [COLOR=#008000]'Final Row of Activesheet in "Work" Workbook in Respective Columns B, C, D, E, F ,G, H[/COLOR]
[COLOR=#008000]
[/COLOR] For i = LBound(CopyArray) To UBound(CopyArray)
[COLOR=#008000]
[/COLOR]Windows(Example).Activate[COLOR=#008000]
[/COLOR]CopyArray(i).Copy

Windows(Work).Activate
.Range(finalRow & PasteArray(i) ).PasteSpecial xlPasteValues

Next i
End Sub

Not sure I am even on the right track. Any guidance with this issue would be great!!

 

[mrmmickle1]

Howard,

Thanks again for all the help. Everything ended up working out great. Your input really helped to speed up a copy/paste task that was taking a long time.
I am always out to improve my VBA game Thanks for the new tricks!

Here is how I ended up using what you taught me:

If OptionButton1 Then
        ws = "YourSHeet"
        plate = Right(PlateName, (Len(PlateName) - Len("M:Your File Directory Here")))  'File string
        plate = Left(plate, (Len(plate) - 5))  'File name only
         ''''Copy Paste Array
        Sheets(ws).Activate
                   finalRow = ActiveSheet.UsedRange.Rows.Count + 1
    Set wkbSource = Workbooks(plate)
                    Set wkbTarget = Workbooks(worklist)
                    Set wksSource = wkbSource.Sheets("Sheet1")
                    Set wksTarget = wkbTarget.Sheets(ws)
                        Set myRng = wksSource.Range("I4,C28,K22,M23,F27,M22,K23,C7,D7,F7,G7,I7,J7,I25,H27,C20,C21,C22,C23,C24,C25,C26,C27,F20,F21,F22,F23,F24,F25,F26,F28,F29,H20,H21,H22,H23")
                        
                    ReDim myArr(myRng.Cells.Count - 1)
                            For Each rngS In myRng
                        myArr(k) = rngS.Value: k = k + 1
                    Next
                        Set myRng = wksTarget.Range("B2,C2,D2,E2,F2,G2,H2,K2,L2,M2,N2,O2,P2,S2,T2,U2,V2,W2,X2,Y2,Z2,AA2,AB2,AC2,AD2,AF2,AG2,AH2,AJ2,AK2,AL2,AM2,AO2,AP2,AQ2,AR2")
                    k = 0
                    For Each rngS In myRng
                    rngS.Range("A" & Cells(Rows.Count, "A").End(xlUp).Row) _
                   .End(xlUp)(2).Value = myArr(k): k = k + 1
                    Next

For the most part the cells were in order, but at some points they skip. I really appreciate your input. It helped a ton

-Matt