Get value from SpinButton to determine the number of loops

Question

Is there any way to get the value of a cell that is connected to a SpinButton and to determine the number of times a data will be copied.

For example everytime you press the Left or Right Button it will subtract or add a value with a minimum of 1 and maximum of 1000.

This is my code so far in copying data.

Range("D3:D10").Copy
Worksheets("Sheet2").Range("A2").PasteSpecial , Transpose:=True

The range of the cell that is associate in the SpinButton is "G7"

I want to get the value of that cell G7 to determine how many times it will copy the data from range D3 to D10.

The values is on "Sheet1". I want it to be pasted on "Sheet2".

Answer 1

Assuming the active sheet has the G7 and range to copy

Range("D3:D10").Copy
Worksheets("Sheet2").Range("A2:A" & 2 + [G7].Value).PasteSpecial , Transpose:=True
Application.CutCopyMode = False

Edit: paste to first available cell in sheet2

-

Sub Copy_Trspose()
    Dim LstRw As Long, pRng As Range, cRng As Range, x
    Dim sh As Worksheet, ws As Worksheet

    Set ws = Sheets("Sheet2")
    Set sh = Sheets("Sheet1")

Application.ScreenUpdating = False

    With sh
        Set cRng = .Range("D3:D10")
        x = .Range("G7").Value
    End With

    With ws
        LstRw = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        Set pRng = .Range(.Cells(LstRw, "A"), .Cells(LstRw + x, "A"))
    End With

    cRng.Copy
    pRng.PasteSpecial , Transpose:=True

    Application.CutCopyMode = False
End Sub