Myujth

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.

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.

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

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.