Create a variable number of tables in vba Excel

[paulomonika]

How can I create a number of tables according to the number indicated in a cell?
Exemple: If I introduce the number 3 in a cell » creates 3 ​​tables (exemple; with 3 rows and 4 lines). If I introduce the number 5 » creates 5 tables with the same number of rows and lines (3 rows and 4 lines)...

[HaHoBe]

Hi, paulomonika,

assuming that Range("A1") will be the cell in which you enter the number, right click on the worksheet tab, choose View Code and copy one of the following codes into the code windows (you may only have one event of that name behind a sheet). Sample1 is for putting tables under each other starting in Column A

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngCounter As Long

Const clngIN_BETWEEN  As Long = 3     'space between the tables
Const clngNO_ROWS     As Long = 3     'count of rows to insert
Const clngNO_COLS     As Long = 4     'count of columns to insert

If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1")) Is Nothing Then
  If Not Target.Value = "" And IsNumeric(Target) Then
    For lngCounter = 1 To Target
      ActiveSheet.ListObjects.Add(xlSrcRange, _
          Range("A" & Rows.Count).End(xlUp).Offset(clngIN_BETWEEN, 0).Resize(clngNO_ROWS, clngNO_COLS), , _
          xlYes).Name = ActiveSheet.Name & Format(Now, "yymmddhhnnss")
    Next lngCounter
  End If
End If
End Sub

Sample2 is for putting the tables next to each other in Row 1:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngCounter As Long

Const clngIN_BETWEEN  As Long = 3     'space between the tables
Const clngNO_ROWS     As Long = 3     'count of rows to insert
Const clngNO_COLS     As Long = 4     'count of columns to insert

If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1")) Is Nothing Then
  If Not Target.Value = "" And IsNumeric(Target) Then
    For lngCounter = 1 To Target
      ActiveSheet.ListObjects.Add(xlSrcRange, _
          Cells(1, Columns.Count).End(xlToLeft).Offset(0, clngIN_BETWEEN).Resize(clngNO_ROWS, clngNO_COLS), , _
          xlYes).Name = ActiveSheet.Name & Format(Now, "yymmddhhnnss")
    Next lngCounter
  End If
End If
End Sub

Ciao,
Holger