VBA to check 2 cells of data and then copy and paste from multiple sheets to the data sheet

[danbates77]

Hi,

I have 7 palletisers at work. G, H, J, K, L, M and N.

Each palletiser has 40 programs. The settings for the programs are listed below each number, so program 1 from each palletiser is in range C3:C38. Program 2 is range D3:D38 and so on.

What I would like to try and do is when I enter the palletiser letter in cell C1 and the palletiser program number in cell C2 on my "DATA" sheet (via data validation) it will then copy and paste that palletiser number settings from each palletiser onto my "DATA" sheet moving across the sheet from column C.

I hope this makes sense but if not then please ask.

I appreciate any help with this.

Thanks
Dan

 

[myall_blues]

How are the different palletisers linked to the settings? Do they all have the same programs and settings?

For example, based on your description the settings for the 40 programs are in C3:AP38, one column for each of the 40 programs. But where is the palletiser letter in all this? Does palletiser G go from C3:AP38, then palletiser H from C40:AP74 or???

A picture would be great.

 

[danbates77]

Hi,

The palletiser letter is just the sheet name and then you are right all the settings are in cells C3:AP38 on each different sheet.

Thanks
Dan

 

[myall_blues]

Try this formula in cell C3 of your DATA sheet.

=INDEX(INDIRECT(C$1&"!C3:AP38"),0,MATCH(C$2,INDIRECT(C$1&"!C3:AP3"),0))

 

[danbates77]

Hi,

Unfortunately your formula isn't working.

I managed to create this code yesterday but it only works for palletiser G and program 1

If I was to carry on like I have then I'd have to copy and edit this code 270 times.

Option Explicit

Sub All_Palletisers()

  Dim myRGB_Red As Long:     myRGB_Red = RGB(255, 0, 0)
  Dim myRGB_Yellow As Long:  myRGB_Yellow = RGB(255, 255, 0)
  Dim myRGB_Blue As Long:    myRGB_Blue = RGB(0, 112, 192)
  Dim myRGB_Amber As Long:   myRGB_Amber = RGB(255, 192, 0)
  Dim myRGB_Green As Long:   myRGB_Green = RGB(0, 176, 80)
  Dim myRGB_Purple As Long:  myRGB_Purple = RGB(112, 48, 160)
  Dim myRGB_Orange As Long:  myRGB_Orange = RGB(237, 125, 49)
  Dim myRGB_Grey As Long:    myRGB_Grey = RGB(166, 166, 166)
  
If Range("C1").Value = "G" And Range("C2").Value = 1 Then

    Sheets("G").Select
    Range("C3:C38").Select
    Selection.Copy
    Sheets("DATA").Select
    Range("C4:C40").Select
    Range("C4").PasteSpecial Paste:=xlPasteValues

    Worksheets("DATA").Range("D1").Value = "H"
    Worksheets("DATA").Range("D2").Value = 1
    Sheets("H").Select
    Range("C3:C38").Select
    Selection.Copy
    Sheets("DATA").Select
    Range("D4:D40").Select
    Range("D4").PasteSpecial Paste:=xlPasteValues

    Worksheets("DATA").Range("E1").Value = "J"
    Worksheets("DATA").Range("E2").Value = 1
    Sheets("J").Select
    Range("C3:C38").Select
    Selection.Copy
    Sheets("DATA").Select
    Range("E4:E40").Select
    Range("E4").PasteSpecial Paste:=xlPasteValues

    Worksheets("DATA").Range("F1").Value = "K"
    Worksheets("DATA").Range("F2").Value = 1
    Sheets("K").Select
    Range("C3:C38").Select
    Selection.Copy
    Sheets("DATA").Select
    Range("F4:F40").Select
    Range("F4").PasteSpecial Paste:=xlPasteValues

    Worksheets("DATA").Range("G1").Value = "L"
    Worksheets("DATA").Range("G2").Value = 1
    Sheets("L").Select
    Range("C3:C38").Select
    Selection.Copy
    Sheets("DATA").Select
    Range("G4:G40").Select
    Range("G4").PasteSpecial Paste:=xlPasteValues

    Worksheets("DATA").Range("H1").Value = "M"
    Worksheets("DATA").Range("H2").Value = 1
    Sheets("M").Select
    Range("C3:C38").Select
    Selection.Copy
    Sheets("DATA").Select
    Range("H4:H40").Select
    Range("H4").PasteSpecial Paste:=xlPasteValues

    Worksheets("DATA").Range("I1").Value = "N"
    Worksheets("DATA").Range("I2").Value = 1
    Sheets("N").Select
    Range("C3:C38").Select
    Selection.Copy
    Sheets("DATA").Select
    Range("I4:I40").Select
    Range("I4").PasteSpecial Paste:=xlPasteValues

    Range("C2:I2").Select
    Selection.Interior.Color = myRGB_Red

End If

End Sub

If you or someone else could look through it and see what I've done and be able to shorten it then that would be great.

Thanks again

Dan

 

[vmjan02]

@ danbates77

I haven't tested the code, but just give it a try. have mention comments so hope you can modify as per your need.

Option Explicit

Sub All_Palletisers()

    Dim myRGB_Red As Long: myRGB_Red = RGB(255, 0, 0)
    Dim myRGB_Yellow As Long: myRGB_Yellow = RGB(255, 255, 0)
    Dim myRGB_Blue As Long: myRGB_Blue = RGB(0, 112, 192)
    Dim myRGB_Amber As Long: myRGB_Amber = RGB(255, 192, 0)
    Dim myRGB_Green As Long: myRGB_Green = RGB(0, 176, 80)
    Dim myRGB_Purple As Long: myRGB_Purple = RGB(112, 48, 160)
    Dim myRGB_Orange As Long: myRGB_Orange = RGB(237, 125, 49)
    Dim myRGB_Grey As Long: myRGB_Grey = RGB(166, 166, 166)
    
    Dim palletisers As Variant
    Dim palletiser As Variant
    Dim programNumber As Integer
    Dim i As Integer
    Dim sourceRange As Range
    Dim destColumn As Integer
    
    ' Define the palletisers
    palletisers = Array("G", "H", "J", "K", "L", "M", "N")
    
    ' Get the selected program number from C2
    programNumber = Range("C2").Value
    
    ' Check if the program number is valid
    If programNumber < 1 Or programNumber > 40 Then
        MsgBox "Please select a valid program number between 1 and 40."
        Exit Sub
    End If
    
    ' Start from column C (3rd column)
    destColumn = 3
    
    ' Loop through each palletiser
    For i = LBound(palletisers) To UBound(palletisers)
        palletiser = palletisers(i)
        
        ' Define the source range based on the program number
        Set sourceRange = Sheets(palletiser).Range(Sheets(palletiser).Cells(3, 2 + programNumber), Sheets(palletiser).Cells(38, 2 + programNumber))
        
        ' Copy the source range
        sourceRange.Copy
        
        ' Paste the values in the corresponding column on the "DATA" sheet
        With Sheets("DATA")
            .Cells(4, destColumn).PasteSpecial Paste:=xlPasteValues
            ' Optionally, you can also clear any existing formatting
            .Cells(4, destColumn).Resize(35).Interior.ColorIndex = xlNone
        End With
        
        ' Increment the destination column
        destColumn = destColumn + 1
    Next i
    
    ' Color the selected cells in the header
    With Worksheets("DATA")
        .Range(.Cells(2, 3), .Cells(2, destColumn - 1)).Interior.Color = myRGB_Red
    End With
    
    ' Clear the clipboard
    Application.CutCopyMode = False

End Sub

 

[danbates77]

Hi,

Thank you so much for your code, it is nearly perfect.

The only problem I have which I need your help with is the colour of the cells in row 2.

I would like the interior colour on row 2 on the Data sheet to match whatever the interior colour of cell C2 is?

Thank you again for your help

Dan

 

[danbates77]

Also after using the code more today, I have found that no matter which palletiser letter I put in cell C1, your code always starts with the data from palletiser G.

If you look at the picture I have attached you can see I have selected palletiser N in cell C1 and at the bottom of the column you can see it has and G in the last cell.

The first and last cell should match just like the other columns.

Any chance you can help me out with this please?

Thanks again

Dan

 

[vmjan02]

Also after using the code more today, I have found that no matter which palletiser letter I put in cell C1, your code always starts with the data from palletiser G.

If you look at the picture I have attached you can see I have selected palletiser N in cell C1 and at the bottom of the column you can see it has and G in the last cell.

The first and last cell should match just like the other columns.

Any chance you can help me out with this please?

Thanks again

Dan

well your #1 post states you have 7 palletiser g,h,....

 

[danbates77]

Hi,

Yes I do have 7 palletisers but what I enter into cells C1 and C2, I would like that data to be the one showing in column C on the Data sheet and then the other palletisers data from that program number (C2) to show along side it in columns D,E,F and so on.

If you could help me with this I'd be very grateful

Thanks again

Dan