Applying looping through columns

Question

I am relatively new to VBA. I am writing a code for a toggle button for a ranged table in excel. The table contains a header row, where the user manually captures a total amount, rows for details where user can opt to capture the details and a footer row which gives a total of the details.

Attached Table Layout

Basically the functions of the button will be to: 1. Hide/Unhide the details rows depending on whether the user wants to capture the details. 2. If the user has captured the details, when hiding back the details, there is a validation on whether the header and footer amounts tally. If not, there is an message prompting the user to overwrite the header (Yes/No/Cancel).

My issue is that I have managed to write the code for a single column, but I do not know how to apply it to the other columns without the need to duplicate the codes. Below is the code I have used, could you please help on the looping:

If ToggleButton1.Value = False Then

    'Copy Totals to Annexe
    Sheets("C. Financials - BS").Select
    ActiveSheet.Range("K10:R10").Select
    Selection.Copy
    Sheets("Ann_Sum").Select
    ActiveSheet.Range("B3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("C. Financials - BS").Select
    ActiveSheet.Range("K19:R19").Select
    Selection.Copy
    Sheets("Ann_Sum").Select
    ActiveSheet.Range("B4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False


            Sheets("Ann_Sum").Select
            If ActiveSheet.Range("B4:B4").Value <> 0 And ActiveSheet.Range("B3:B3").Value <> ActiveSheet.Range("B4:B4").Value Then
                Sheets("C. Financials - BS").Select
                ActiveSheet.Range("K10").Select
                Range("K10").Interior.Color = RGB(244, 176, 132)
                Select Case MsgBox("Detailed Entries do not correspond to Header Total(s). Do you want to overwrite the Total ?'", _
                    vbYesNoCancel + vbExclamation)
                    Case Is = vbYes
                        Sheets("Ann_Sum").Select
                        ActiveSheet.Range("B4").Select
                        Selection.Copy
                        Sheets("C. Financials - BS").Select
                        ActiveSheet.Range("K10").Select
                        Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
                         , SkipBlanks:=False, Transpose:=False

                        Sheets("C. Financials - BS").Select
                        Rows("11:19").Hidden = True
                        ActiveSheet.Range("K10").Select
                        Range("K10").Interior.Color = RGB(214, 220, 228)
                        ActiveSheet.Range("I10:R10").Select
                        Selection.Font.Bold = False
                        ActiveSheet.Range("I1").Select
                    Case Is = vbNo
                        Sheets("C. Financials - BS").Select
                        Rows("11:19").Hidden = True
                        ActiveSheet.Range("I10:R10").Select
                        Selection.Font.Bold = False
                        ActiveSheet.Range("I1").Select
                    Case Is = vbCancel
                        'Set up procedure to cancel close
                        Cancel = True
                        GoTo ErrHandler
                End Select
            End If

            Sheets("Ann_Sum").Select
            If ActiveSheet.Range("B3:B3").Value = ActiveSheet.Range("B4:B4").Value Then
               Sheets("C. Financials - BS").Select
               Rows("11:19").Hidden = True
               ActiveSheet.Range("K10").Select
               Range("K10").Interior.Color = RGB(214, 220, 228)
               ActiveSheet.Range("I10:R10").Select
               Selection.Font.Bold = False
               ActiveSheet.Range("I1").Select
            End If



Sheets("C. Financials - BS").Select
Rows("11:19").Hidden = True
ActiveSheet.Range("I10:R10").Select
Selection.Font.Bold = False
ActiveSheet.Range("I1").Select

End If

Some advice - try to avoid using `Select` https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba#:~:text=Probably%20the%20biggest%20thing%20you,names)%20in%20your%20VBA%20code. — jamheadart, Jun 11 '20 at 11:20
With such a question it is very difficult to be helped... I do not understand too much from it. Which column is that "single column" you say that the code processes? I can see B, I, K columns involved. Do you want meaning column B:B. That's why you used strange range definitions like `Range("B3:B3")`? — FaneDuru, Jun 11 '20 at 11:52

Applying looping through columns

0 Answers0