VBA code that loops through a range and copies and pastes the data below

[danbates77]

Hi,

Please can someone help me?

I have a file with two sheets on "DATA" & G
On sheet G I have my information and in cell C2:AP2 I have numbers from 1-40 with certain background colours.
On my DATA sheet I have cells C1 & C2. C1 is for the palletiser letter and C2 is the program number 1-40.
I already have my code that once I select the palletiser letter & number it enters the data below.
Thank you @Joe4

Sub Palletiser_G1()
    Dim ws As Worksheet
    Dim i As Long
'   Set current worksheet
    Set ws = ActiveSheet
    
    i = Range("C2") + 0
    If (Range("C1") = "G") And (i >= 1) And (i <= 40) Then
        Sheets("G").Activate
        Range(Cells(3, i + 2), Cells(38, i + 2)).Copy
        ws.Activate
         Range("C4").PasteSpecial Paste:=xlPasteValues
         Range("C1").Select
    End If
End Sub

What I would like now is to extend this code so once I select the palletiser number in C2 it enters the data below as normal but then it finds all the other numbers with the same coloured background and the copies and pastes them in columns D & E & F and so on depending on how many are of the same colour.

I am struggling to explain this easily so if you have any questions then please ask.

Thanks Dan

 

[Sektor]

Your code doesn't correlate to your initial problem. Your data on sheet "G" is in range A2:AP2 (row), but your code refers to column (Range(Cells(3, i + 2), Cells(38, i + 2)).

 

[danbates77]

The data I have on sheet G runs in columns, so program 1 is in column C and runs from C3:C37 and so on up to program 40.

How do you mean my data on sheet G is in the range A2:AP2? The palletiser program numbers are in range C2:AP2.

Thanks
Dan