Loop for copying selected table repeatedly when clicking the variation or contract button-CodePudding

My first time on stack overflow. I'm trying to create a repeated copy and paste of a table i have in one of my worksheets and assign it to the following buttons in my budget worksheet. For example when ever you click on the contract button, it will install the new table. Click it again, it will leave a line break and insert the new table. Same thing with the variation button.

Budget worksheet

Table sample

My attempt in a related copied sample in one of the questions asked here.

I understand to copy and paste I've been told to use this code

Sub CopyPasteToAnotherSheet()
    Worksheets("Dataset").Range("B2:F9").Copy Worksheets("CopyPaste").Range("B2")
End Sub

It works, but I can only copy it once. I dont know how to copy it numerous times each time I run the macro.

If anyone has an idea on how to do this, would be much appreciated. (I keep failing to do this)

CodePudding user response：

Welcome to stack!

So I think I understand the want, it's usually good to have a go and show us the code you have but as its the first time here's a start of a code that might do that:

Sub variations()
    Dim rng As Range
    Set rng = ActiveWorkbook.Sheets("Variations").Range("A1:B5")
    Run copyAcross(rng)
End Sub

Sub contracts()
    Dim rng As Range
    Set rng = Worksheets("Contract").Range("A1:B2")
    Run copyAcross(rng)
End Sub

Function copyAcross(rng As Range)
    Dim ws As Worksheet
    Set ws = ActiveWorkbook.Sheets("Budget")
    targetRow = ws.Range("A" & Rows.Count).End(xlUp).Row   2
    ws.Cells(targetRow, "A").Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value
End Function

this gives two subs that the buttons can be pointed to. In each sub you can define the range you want and the function will copy it across.

Note: for this to work column A in the Budget file would need at least some value somewhere in it, the first copy would then be put two rows below that