Repeating macro for adding rows & columns

[GaryCarbon]

Hello folks,

I've noticed a few posts on this topic but I've not been able to solve my problems so far so apologies if I've duplicated another post!

I'm trying to design a spreadsheet for some of my clients to use to help them record information from a number of sites. What I want to be able to do is to have them click a button and a new site field appears at the bottom of the list on the first sheet and a new table is added on a second sheet. I've managed to record a macro to do this once, but I've not been able to get it to loop or repeat itself and add the information to the next blank section.

Because I'm not sure how well I've articulated what I want it to do please see below for example

Sheet 1

Button
Site 1	1

<tbody>
</tbody>

Sheet2

Site 1	A	B	C
Jan	x	y	z
Feb	x	y	z

<tbody>
</tbody>

After using the button I want it to repeatedly add information into the next blank space as below
Sheet1

Button
Site 1	1
Site 2	1
Site 3	1

<tbody>
</tbody>

Sheet 2

Site 1	A	B	C	Site 2	A	B	C	Site 3	etc
Jan	x	y	z		x	y	z
Feb	x	y	z		x	y	z

<tbody>
</tbody>

This is what I have so far that just adds one new row & all associated formatting:

 Range("A5:M5").Select
    Selection.AutoFill Destination:=Range("A5:M6"), Type:=xlFillDefault
    Range("A5:M6").Select
    Sheets("Data").Select
    Range("B2:J4").Select
    Selection.Copy
    Range("K2").Select
    ActiveSheet.Paste
    Range("K5:S16").Select
    Application.CutCopyMode = False
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
End Sub

Does anyone have any advice on how I can make this repeat? Some clients need this for about 400 sites so the more automatic I can make it the better!

 

[Michael M]

I've cleaned up the code a bit, but I still don't follow exactly what you want

Sub MM1()
Range("A5:M5").AutoFill Destination:=Range("A5:M6"), Type:=xlFillDefault
Sheets("Data").Range("B2:J4").Copy Destination:=Range("K2")
With Range("K5:S16").Borders
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
End Sub