VBA Code to paste to multiple sheets

[AlannaK]

Hi everyone,
I am really new to VBA coding and am trying to figure out how to copy the data from one sheet ("Search") across to two different sheets ("Tracker" and "Reporting"). Reporting is Sheet7.
So far, i have the code for the first Copy as this is a straightforward copy and paste (transposing the data) however, the second Copy is a condensed version, so i don't need the same data range to be copied, only selected fields.
Is there a way to combine these sort of things. this is what i have so far for the first copy which works;

Sheets("Search").Range("C4:C57").Copy
Sheets("Tracker").Cells(Rows.Count, "A").End(xlUp).Offset(1). _
PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False

And this is the other copy (condensed version) but i would like to link these if possible in the same module.

erw = Sheet7.Cells(1, 1).End(xlDown).Row + 1

Sheet7.Cells(erw, 1) = Range("C4")
Sheet7.Cells(erw, 2) = Range("C7")
Sheet7.Cells(erw, 3) = Range("C14")
Sheet7.Cells(erw, 4) = Range("C20")
Sheet7.Cells(erw, 5) = Range("C9")
Sheet7.Cells(erw, 6) = Range("C8")
Sheet7.Cells(erw, 7) = Range("C18")
Sheet7.Cells(erw, 8) = Range("C5")
Sheet7.Cells(erw, 9) = Range("C12")
Sheet7.Cells(erw, 10) = Range("C11")
Sheet7.Cells(erw, 11) = Range("C48")
Sheet7.Cells(erw, 12) = Range("C26")
Sheet7.Cells(erw, 13) = Range("C26")

 

[vcoolio]

Hello Alanna,

See if this helps:-

Option Explicit
Sub Test()

        Dim wsSe As Worksheet: Set wsSe = Sheets("Search")
        Dim wsTr As Worksheet: Set wsTr = Sheets("Tracker")
        Dim wsRe As Worksheet: Set wsRe = Sheets("Reporting")
        Dim cAr As Variant, pAr As Variant, i As Long, nrow As Long
        
        cAr = Array("C4", "C7", "C14", "C20", _
              "C9", "C8", "C18", "C5", "C12", "C11", "C48", "C26", "C27")
        pAr = Array("A", "B", "C", "D", "E", _
              "F", "G", "H", "I", "J", "K", "L", "M")
              
        nrow = wsRe.Cells(Rows.Count, 1).End(xlUp).Row + 1
       
Application.ScreenUpdating = False
        
        wsSe.Range("C4:C57").Copy
        wsTr.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues, Transpose:=True
        
        For i = 0 To UBound(cAr)
                wsSe.Range(cAr(i)).Copy
                wsRe.Range(pAr(i) & nrow).PasteSpecial xlValues
        Next i
        
Application.CutCopyMode = False
Application.ScreenUpdating = True
        
End Sub

I hope that this helps.

Cheerio,
vcoolio.