Excel VBA Increment Range by 1 across Subs

Question

Due to the last question not being very clear on my part. I am novice and will need a bit of guidence in how to implement any answers.

I currently have the following code spread over different buttons and therefore different Subs. For the sake of clarity, I have seperated the ranges that i need to increment by 1 from the code and boldened the values i need to count up by 1 each button press. I appreciate this breaks the format, I apologise.

The number increase will occur after clicking the "InsertNewBill" button:

Private Sub InsertNewBill_Click()
    'I AM USING i TO STORE THE CELL INCREMENT, IT CURRENTLY DOES NOTHING**
    Dim i As Integer

range("A30:AC30").Select

range("AC30").Activate

    Selection.Copy
    Selection.Insert Shift:=xlDown
End Sub

Private Sub DeleteTickBoxes_Click()
    'Variables
    Dim c As CheckBox
    Dim CellRange As Range
    Dim cel As Range

Set CellRange = ActiveSheet.Range("E7:F30")

    'Delete Checkboxes within the specified range above on the ActiveSheet Only
    For Each c In ActiveSheet.CheckBoxes
        If Not Intersect(c.TopLeftCell, CellRange) Is Nothing Then
            c.Delete
        End If
    Next    
    'Insert New Checkboxes and Assign to a specified link cell using the offset
    For Each cel In CellRange
        'you can adjust left, top, height, width to your needs
        Set c = ActiveSheet.CheckBoxes.Add(cel.Left, cel.Top, 30, 6)
        With c   'Clears the textbox so it has no text
            .Caption = ""
            'Offset works by offsetting (Row offset, Column Offset) and accepts
            'positive for down/right and negative for left/up,
            'keep in not that the linked cells will automatically populate with true/false
            .LinkedCell = cel.Offset(0, -4).Address
        End With
    Next
    Call CentreCheckbox_Click
End Sub

I need all boldened values to increase by one. I.e from F30 to F31 and A30:AC30 to A31:AC31. This value also needs to be carried across from the InsertNewBill_Click sub to the DeleteTickBoxes_Click sub.

I assume i will need to remove the Private sub and possibly have a public integer variable? Im just not sure how to implement increasing only the number by 1 after each button click.

All your help is appreciated

Answer 1

Use a public variable and concatenate that into your address. Initialize the value as, say 30, in the workbook open event.

ThisWorkbook:

Option Explicit
Public i As Long
Private Sub Workbook_Open()
    i = 30
End Sub

Standard module:

Option Explicit
Private Sub button2_Click()
    ThisWorkbook.i = ThisWorkbook.i + 1
    Debug.Print ActiveSheet.Range("E" & ThisWorkbook.i & ":F" & ThisWorkbook.i).Address
End Sub

Answer 2

You should try to stop using Select and Activate - in this case you can just Copy the Range:

Range(cells(30,"A"),cells(30,"AC")).Copy

To add i as an offset, you could use:

Range(cells(30+i,"A"),cells(30+i,"AC")).Copy

Or you could use:

Range(cells(30,"A"),cells(30,"AC")).Offset(i,0).Copy

To make i work across other Subs in the Workbook, declare it Public.

Unless you're sure the value will remain low, I'd suggest declaring it as a Long, not an Integer.

For the Set CellRange = ActiveSheet.Range("E7:F30") setting, you could use:

Set CellRange = ActiveSheet.Range("E7:F" & 30 + i)

Answer 3

For those interested. Here is the VBA that i ended up using as it fits my purpose.

Sub InsertNewBill_Click()
Dim rngCopy As range
Dim rngPaste As range
Dim clearCell As range

i = Cells(30, 1) + 1
    Cells(30, 1) = i

ActiveSheet.range("A" & i & ":AD" & i).Insert

Set rngPaste = range("A" & i & ":AD" & i)
Set rngCopy = ActiveSheet.range("A" & i - 1 & ":AD" & i - 1)

rngCopy.Copy
rngPaste.PasteSpecial Paste:=xlPasteAll

With ActiveWorkSheet
    Cells(i, 1).ClearContents
End With

Call DeleteTickBoxes_Click

End Sub

*

Private Sub DeleteTickBoxes_Click()

'Variables
Dim c As CheckBox
Dim CellRange As range
Dim cel As range

i = Cells(30, 1)

Set CellRange = ActiveSheet.range("F" & i & ":G" & i)

'Delete Checkboxes within the specified range above on the ActiveSheet Only
For Each c In ActiveSheet.CheckBoxes
If Not Intersect(c.TopLeftCell, CellRange) Is Nothing Then
c.Delete
End If

Next

'Insert New Checkboxes and Assign to a specified link cell using the offset
For Each cel In CellRange
    Set c = ActiveSheet.CheckBoxes.Add(cel.Left, cel.Top, 30, 6) 'you can adjust left, top, height, width to your needs
    With c
        'Clears the textbox so it has no text
        .Caption = ""

        'Offset works by offsetting (Row offset, Column Offset) and accepts positive for down/right and negative for left/up,
        'keep in not that the linked cells will automatically populate with true/false
        .LinkedCell = cel.Offset(0, -4).Address

    End With

Next
Call CentreCheckbox_Click

End Sub