Pressing a button to cycle through a range's values

Question

I'm trying to create a button to automatically fill in cell B5 with information from another sheet's cells A1:A10.

When the button is pressed I want B5 to contain the info from cell A1. Then, when the button is pressed again, it should contain the info from A2 and so on.

Answer 1

Here is short simple method.

You will need a counter to increment with each button press. You will need to put this somewhere on your spreadsheet, for this example it's below the button.

1. First Decide where your counter will go, for this example it will be directly below the button.

2. Insert your Button.

3. Assign Macro to your Button, make sure you save it to the workbook.

4. Paste below code into VBA Editor for your Button Click.

Code

Sub Button1_Click()

    Dim CopySheet As Worksheet, PasteSheet As Worksheet
    Dim xFrom As Integer, xTo As Integer, i As Integer
    Dim pasteCell As String, cCell As String

    'Sheets
    Set CopySheet = Worksheets("Sheet2") 'Sheet you are copying from.
    Set PasteSheet = Worksheets("Sheet1")  'Sheet you are pasting into.

    'Rows, range of rows start from row rStart to rEnd
    rStart = 1 'Start of Row you want to copy from.
    rEnd = 10 'End of Row you want to copy from.

    'Cells
    pasteCell = "B5" 'Cell we will paste data from CopySheet.

    'Counter will increments with each button press.
    cCell = "E5" 'Change "E5" to reference cell on your spreadsheet.
    i = Range(cCell).Value

    Application.ScreenUpdating = False 'We disable Screen Updating to prevent interruption.

    'Update Counter
    i = i + 1
    If (i > rEnd) Then
        i = rStart
    End If
    Range(cCell).Value = i

    'Copy/Paste Functions
    CopySheet.Select
    Range("A" & i).Select
    Selection.Copy
    PasteSheet.Select
    Range(pasteCell).Select
    ActiveSheet.Paste

    Application.ScreenUpdating = True 'Enable Screen Updating at end of operation.
End Sub

The button will copy based on the counter number plus 1, so if the number is 0 on button press the macro will add get the 0 + 1 then begin the copy and paste functions.

Answer 2

There's no need to store a counter in a cell in the workbook. You can use a static variable instead.

Paste the following code into any non-class module:

'============================================================================================
' Module     : <any non-class module>
' Version    : 0.1.1
' Part       : 1 of 1
' References : N/A
' Source     : https://superuser.com/a/1331173/763880
'============================================================================================

Option Explicit

Public Sub Next_Click()

  Const s_DestSheet As String = "Sheet1"
  Const s_DestRange As String = "B5"
  Const s_SrcSheet As String = "Sheet2"
  Const s_SrcCell As String = "A1:A10"

  Static sidxCurrentCell As Variant: If IsEmpty(sidxCurrentCell) Then sidxCurrentCell = -1

  With Worksheets(s_SrcSheet).Range(s_SrcCell)
    sidxCurrentCell = (sidxCurrentCell + 1) Mod .Cells.Count
    .Cells(sidxCurrentCell + 1).Copy Destination:=Worksheets(s_DestSheet).Range(s_DestRange)
  End With

End Sub

Then assign it to your button.

The only issue with this code is that it doesn't remember which cell it was up to when you re-open the workbook, and restarts from the first cell. This can be worked around if desired.

---

Addendum:

If you also wish to have a "Previous" button to cycle backwards, it gets slightly trickier - you need a generalised Previous/Next subroutine with a parameter to determine the direction. Then, each button needs to be assigned to separate subroutines that call the main routine with the appropriate argument:

'============================================================================================
' Module     : <any non-class module>
' Version    : 0.2.0
' Part       : 1 of 1
' References : N/A
' Source     : https://superuser.com/a/1331173/763880
'============================================================================================
Option Explicit

Private Sub Next_or_Previous( _
                                       ByRef direction As Long _
                            )
        Dim plngDirection As Long: plngDirection = direction

  Const s_DestSheet As String = "Sheet1"
  Const s_DestRange As String = "B5"
  Const s_SrcSheet As String = "Sheet2"
  Const s_SrcCell As String = "A1:A10"

  Static sidxCurrentCell As Variant: If IsEmpty(sidxCurrentCell) Then sidxCurrentCell = -plngDirection

  With Worksheets(s_SrcSheet).Range(s_SrcCell)
    sidxCurrentCell = (sidxCurrentCell + plngDirection + .Cells.Count) Mod .Cells.Count
    .Cells(sidxCurrentCell + 1).Copy Destination:=Worksheets(s_DestSheet).Range(s_DestRange)
  End With

End Sub

Public Sub Previous_Click()
  Next_or_Previous -1
End Sub

Public Sub Next_Click()
  Next_or_Previous 1
End Sub

Answer 3

My approach is quit different to solve the issue.

I would like to suggest Worksheet Selection Change Event instead of Button Click , since it avoids the tedious Loop method task.

Worksheet Selection Change almost works like Button Click, since for 10 items 10 Clicks are required, as well as Selection Change Event also needs similar Clicks, and the best part is the order, it could be Ascending/Descending or even Random.

Below written code will copy cells from designated Data Range A1:A10 on Mouse Click, to destination Sheet's Cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
    With Sheets("Sheet2")
        .Select
        .Range("B5").Value = Target.Value
    End With
End If
End Sub

How the Macro works:

• Click any Cell between A1:A10 at Source Sheet to Copy to Destination Sheet's Cell B5.

Note, Source Range A1:A10, destination Sheet's name Sheet2 & Cell B5 are editable.