Refining my code?

[danbates77]

Hi,

Please can someone help me?

I have 7 palletisers at work (G,H,J,K,L,M & N) and each palletiser has 40 programs (1-40).

I would like to have a sheet (DATA) where I can compare up to 10 programs side by side (C1:L1 for the palletiser letter and C2:L2 for palletiser number) at any one time from any palletiser.

I have created my sheet and I have coded 1 palletiser so far but the way I know how to do it means I have 40 lines of code per palletiser.

I was wondering if someone could take a look and see if you know how to shorten it?

Sub Check_Palletiser_Program_Number()

Application.EnableEvents = False

If Range("C1") = "" Or Range("C2") = "" Then Range("C4:C37").ClearContents

If Range("C1") = "G" And Range("C2") = "1" Then Range("C4:C37").Value = Sheets("G").Range("C4:C37").Value
If Range("C1") = "G" And Range("C2") = "2" Then Range("C4:C37").Value = Sheets("G").Range("D3:D37").Value
If Range("C1") = "G" And Range("C2") = "3" Then Range("C4:C37").Value = Sheets("G").Range("E4:E37").Value
If Range("C1") = "G" And Range("C2") = "4" Then Range("C4:C37").Value = Sheets("G").Range("F4:F37").Value
If Range("C1") = "G" And Range("C2") = "5" Then Range("C4:C37").Value = Sheets("G").Range("G4:G37").Value
If Range("C1") = "G" And Range("C2") = "6" Then Range("C4:C37").Value = Sheets("G").Range("H4:H37").Value
If Range("C1") = "G" And Range("C2") = "7" Then Range("C4:C37").Value = Sheets("G").Range("I4:I37").Value
If Range("C1") = "G" And Range("C2") = "8" Then Range("C4:C21").Value = Sheets("G").Range("J4:J37").Value
If Range("C1") = "G" And Range("C2") = "9" Then Range("C4:C21").Value = Sheets("G").Range("K4:K37").Value
If Range("C1") = "G" And Range("C2") = "10" Then Range("C4:C21").Value = Sheets("G").Range("L4:L37").Value
If Range("C1") = "G" And Range("C2") = "11" Then Range("C4:C21").Value = Sheets("G").Range("M4:M37").Value
If Range("C1") = "G" And Range("C2") = "12" Then Range("C4:C21").Value = Sheets("G").Range("N4:N37").Value
If Range("C1") = "G" And Range("C2") = "13" Then Range("C4:C21").Value = Sheets("G").Range("O4:O37").Value
If Range("C1") = "G" And Range("C2") = "14" Then Range("C4:C21").Value = Sheets("G").Range("P4:P37").Value
If Range("C1") = "G" And Range("C2") = "15" Then Range("C4:C21").Value = Sheets("G").Range("Q4:Q37").Value
If Range("C1") = "G" And Range("C2") = "16" Then Range("C4:C21").Value = Sheets("G").Range("R4:R37").Value
If Range("C1") = "G" And Range("C2") = "17" Then Range("C4:C21").Value = Sheets("G").Range("S4:S37").Value
If Range("C1") = "G" And Range("C2") = "18" Then Range("C4:C21").Value = Sheets("G").Range("T4:T37").Value
If Range("C1") = "G" And Range("C2") = "19" Then Range("C4:C21").Value = Sheets("G").Range("U4:U37").Value
If Range("C1") = "G" And Range("C2") = "20" Then Range("C4:C21").Value = Sheets("G").Range("V4:V37").Value
If Range("C1") = "G" And Range("C2") = "21" Then Range("C4:C21").Value = Sheets("G").Range("W4:W37").Value
If Range("C1") = "G" And Range("C2") = "22" Then Range("C4:C21").Value = Sheets("G").Range("X4:X37").Value
If Range("C1") = "G" And Range("C2") = "23" Then Range("C4:C21").Value = Sheets("G").Range("Y4:Y37").Value
If Range("C1") = "G" And Range("C2") = "24" Then Range("C4:C21").Value = Sheets("G").Range("Z4:Z37").Value
If Range("C1") = "G" And Range("C2") = "25" Then Range("C4:C21").Value = Sheets("G").Range("AA4:AA37").Value
If Range("C1") = "G" And Range("C2") = "26" Then Range("C4:C21").Value = Sheets("G").Range("AB4:AB37").Value
If Range("C1") = "G" And Range("C2") = "27" Then Range("C4:C21").Value = Sheets("G").Range("AC4:AC37").Value
If Range("C1") = "G" And Range("C2") = "28" Then Range("C4:C21").Value = Sheets("G").Range("AD4:AD37").Value
If Range("C1") = "G" And Range("C2") = "29" Then Range("C4:C21").Value = Sheets("G").Range("AE4:AE37").Value
If Range("C1") = "G" And Range("C2") = "30" Then Range("C4:C21").Value = Sheets("G").Range("AF4:AF37").Value
If Range("C1") = "G" And Range("C2") = "31" Then Range("C4:C21").Value = Sheets("G").Range("AG4:AG37").Value
If Range("C1") = "G" And Range("C2") = "32" Then Range("C4:C21").Value = Sheets("G").Range("AH4:AH37").Value
If Range("C1") = "G" And Range("C2") = "33" Then Range("C4:C21").Value = Sheets("G").Range("AI4:AI37").Value
If Range("C1") = "G" And Range("C2") = "34" Then Range("C4:C21").Value = Sheets("G").Range("AJ4:AJ37").Value
If Range("C1") = "G" And Range("C2") = "35" Then Range("C4:C21").Value = Sheets("G").Range("AK4:AK37").Value
If Range("C1") = "G" And Range("C2") = "36" Then Range("C4:C21").Value = Sheets("G").Range("AL4:AL37").Value
If Range("C1") = "G" And Range("C2") = "37" Then Range("C4:C21").Value = Sheets("G").Range("AM4:AM37").Value
If Range("C1") = "G" And Range("C2") = "38" Then Range("C4:C21").Value = Sheets("G").Range("AN4:AN37").Value
If Range("C1") = "G" And Range("C2") = "39" Then Range("C4:C21").Value = Sheets("G").Range("AO4:AO37").Value
If Range("C1") = "G" And Range("C2") = "40" Then Range("C4:C21").Value = Sheets("G").Range("AP4:AP37").Value

Application.EnableEvents = True

End Sub

Hope this makes sense and any help would be appreciated

Thanks

Dan

 

[Joe4]

OK, let's try breaking this out into more steps that it should require, so we can try to see where things are going wrong.
Step through the code one line at a time using the F8 key, while watching your data.
Many times, as you watch what it is doing each step of the way, you can often see exactly what is happening and where the issue is.

    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(4, i + 2), Cells(37, i + 2)).Copy
        ws.Activate
        Range("C4").Select
        ActiveSheet.Paste
    End If

 

[danbates77]

That has worked but it has also copied the cell boarders

 

[Joe4]

Try changing these lines:

        Range("C4").Select
        ActiveSheet.Paste

to this:

        Range("C4").PasteSpecial Paste:=xlPasteValues

to copy just the values.

 

[danbates77]

That's perfect now, thank you.

 

[Joe4]

You are welcome.

 

[SeniorNewbie]

Hi Dan and all helpers so far!

I'm a little confused that no one thinks to change the cell.addresses from Range("Xn") to Cells(r,c). [rows, columns]. Because then it's much easier to use loops through iterators only.
Or is it me who is far from an intelligent solution?

??Senior Newbie

 

[Joe4]

Hi Dan and all helpers so far!

I'm a little confused that no one thinks to change the cell.addresses from Range("Xn") to Cells(r,c). [rows, columns]. Because then it's much easier to use loops through iterators only.
Or is it me who is far from an intelligent solution?

??Senior Newbie

Do you mean like in posts 3, 5, and 9???


Actually, upon further inspection, there was no need to use loops at all!
Whenever possible, loops SHOULD be avoided if possible, as they are slow and inefficient in your code.
Sometimes you have to use them, but if there are alternatives, it is usually better to use those.