What is a good Excel VBA macro to copy a table from another worksheet based on numerical value of a cell?

[TheJayJay]

I am looking for a macro to have cells A14:F22 (Sheet2) copied to Sheet1 under cell A13 based on the numerical value of cell A13. For example, if cell A13 (Sheet1) has a value of 1 then cells A14:F22 (Sheet2) would get copied under A13 (Sheet1) once. If cell A13 has a value of 2 then cells A14:F22 (Sheet2) would get copied under A13 (Sheet1) twice (one under the other)

1

A B C D E F

or

2

A B C D E F
A B C D E F

or

3

A B C D E F
A B C D E F
A B C D E F

So on and so forth

 

[Vogateer]

Something like this might work:

Sub copy_n_times()
    
    ' Range to copy
    Dim copy_range As Range, copy_range_rows As Long
    Set copy_range = Sheets(2).Range("A14:F22")
    copy_range_rows = copy_range.Rows.Count
    
    ' How many times to copy based on value of A12
    Dim times_to_copy As Long
    times_to_copy = Sheets(1).Range("A13").Value2
    
    ' Array to paste after multiplying by times_to_copy
    Dim paste_array As Variant
    paste_array = copy_range.Value2
    
    ' Duplicate array number of times needed to paste
    Dim array_rows As Long
    array_rows = copy_range_rows * times_to_copy
    ReDim Preserve paste_array(1 To 9, 1 To array_rows)
    
    Dim i As Long
    
    For i = 1 To times_to_copy
        With Sheets(1)
            .Range(.Cells(14 + ((i - 1) * copy_range_rows), 1), .Cells(13 + (i * copy_range_rows), 6)).Value = paste_array
            End With
        Next
    
    End Sub

 

[TheJayJay]

Thank you so much. However, I noticed that the macro replaces the text in my fields when I run it again. What if I want to have lets say instead of having the macro covering A14:F22 I want it to cover A14:F14 and depending on the numerical value of A13 in Sheet1 I would like it to skip 5 lines and paste A14:F14. This would I believe not delete my information that I would place in the middle of the fields

 

[Vogateer]

It might be a while before I can look at this again, but I think I might need an example of how you'd like it to work, preferably showing me what the sheet should look like before and after running the script.

If you want to paste the information after the last used row, we could do that too. I'm just having a hard time figuring out what the sheet is supposed to be doing.