CODEWITHSUNDEEP
vbaexcel
d123
d123

Reputation: 9

vba excel macro button, change only a specific cell, replacing with different data in other sheet

How do I get a button to change one cell value each time the button is pressed,

lets say cell B5 is the active cell and I want this value to changed every time I click the button based on the values in a different sheet A1:A10,just going down the list.

Sub Button2_Click()
Sheets("data").Range("A1:A10").Copy
Sheets("printing").Range("B5").PasteSpecial xlPasteValues
End Sub

I want to create a sort of loop, example I'm printing the same file many times but changing the names(in a cell), I have a list of names in a sheet called data, every time I click the button I want the (different sheet called printing) cell B5 to change.

I can simply do this with copy and paste however its about 250 names I was hoping for a simpler solution.

Upvotes: 0

Views: 888

Answers (1)

Taylor Raine
Taylor Raine

Reputation: 578

It think that this is about what you are looking for.

The subroutine below sets the value of cell B5 on sheet 2 to be the value of the cell that corresponds to the index, iterating over down columns then rows (A1, A2, ... A20, B1, B2, B20 in the example below)

You can learn more about the static statement, which makes this work here.

 Sub iterateStatic(Optional Reset As Boolean = False)
    Static index    As Integer
    Dim fromRng     As Range, _
        targetRng   As Range
    If Reset Then index = 0
    Let index = index + 1
    Set fromRng = Sheets(1).Range("A1:B20")
    Set targetRng = Sheets(2).Range("B5")
    Let targetRng.Value = fromRng.Cells.Item( _
            RowIndex:=index Mod fromRng.Rows.Count, _
            ColumnIndex:=-Int(-index / fromRng.Rows.Count)).Value

'    ''  Debug to show how the function iterates over the range
'
'    Debug.Print fromRng.Cells.Item( _
'            RowIndex:=index Mod fromRng.Rows.Count, _
'            ColumnIndex:=-Int(-index / fromRng.Rows.Count)).Address

End Sub

Upvotes: 0

Related Questions