Create tables dynamically using cell value

[mdwasim]

Hello Experts, A happy new year to all!

I have been searching a way to generate tables in a sheet base on the value of cell (drop down list).
If you look at the below screenshot

1. I have a pre-defined format of table named as "TemplateTable"
2. 2xdropdown list which is dynamically populated. Number of Rows to create, Number of Tables to create, and group rows by number

What I am trying to figure out is how to create tables based on the TemplateTable on the fly.
I am not very good at VBA, but I try best to understand it.

If someone has got time to assist me that would be great!

 

[igold]

Hi mdwasim,

Welcome to the MrExcel Forum.

This should not be hard but I am not sure what you mean with the term "grouping". I see from your graphic that you have group numbers to left of the tables, but that is not telling me anything. Additionally, I notice that you are grouping by 3 and creating 3 tables, is that a coincidence. Also, what is the location (Sheet Name) where the "TemplateTable" resides.

Your graphic is good. With your current dropdown selections would there be another table below Group 2. Perhaps you could add more tables to it to show what it would look like with different numbers in your dropdowns (don't bother with the rows).

 

[Norie]

What exactly is 'TemplateTable '?

 

[mdwasim]

Thanks for your time guys.

"TemplateTable" is going to be a pre-designed table, in same .xlsm and in a hidden sheet.
Yes, there will be tables below as well, may be "TemplateTable2"
These "TemplateTable"/s are as a format which I can change later. e.g., if needed to add a column. when changes are done to TemplateTable, those will be reflected when new task is initiated.
The "WorkingSheet" is where user interact.
They select from dropdown lists, like 2 Tables, with 6 rows and group those rows by 3 or 4.
The grouping will automatically add letters "A","B","C" in 1st column of each table.
Once the table/s structure is created, they can fill in the details in cells where data need to be captured.

 

[igold]

Can you tell us the name of the hidden sheet.

 

[mdwasim]

It is "Backend"

 

[mdwasim]

By the way, how can we handle a scenario, where user 1st select "3" tables and then decides to use only "2" tables? will be good if the last table i.e., "3rd" gets removed.

 

[mdwasim]

Cool, as a beginners setup, I managed to add/remove rows to a table using a button (later will figure out using to dropdown)
Now, I am planning to protect the sheet and allowing users to only enter data in "Unlocked" cells.
When I enable the protection, those buttons are not able to run the VBA procedures!.
I did check some articles stating use of UserInterfaceOnly:=true.
Not sure what exactly that means, and how do I define what is covered in userinterface!.

 

[igold]

I am unsure as to what Rows and Columns are being shown in your graphic. This code will take the number of Rows from the value in Cell A3 and the number of Tables from Cell D3 on your "WorkingSheet". The Tables will be created starting in Cell B7. I am still not real clear what you want done with the grouping, but perhaps this will get you closer to where you need to be.

Sub CreateTables()

    Dim tblTempl As Object, ActiveTable As ListObject
    Dim wsWS As Worksheet: Set wsWS = Worksheets("WorkingSheet")
    Dim wsBE As Worksheet: Set wsBE = Worksheets("Backend")
    Dim rw As Long, grp As Long, tbls As Long, i As Long, nxt As Long
    Dim objNew As ListObject
    Dim rng As Range
    
    Application.ScreenUpdating = False
    With wsWS
        tbls = .Range("D3")
        rw = .Range("A3")
        .Range("B7").Select
    End With
    
    For i = 1 To tbls
        If i = 1 Then nxt = 7
        Set tblTempl = Worksheets("Backend").ListObjects("TemplateTable")
        wsBE.ListObjects("TemplateTable").Range.Copy wsWS.Range("B" & nxt)
        wsWS.Range("A" & nxt) = "Table " & i
        Set objNew = wsWS.ListObjects(i)
        objNew.Name = "NewTable" & i
        Set rng = Range("NewTable" & i & "[#All]").Resize(rw + 1, ActiveSheet.ListObjects("NewTable" & i).Range.Columns.Count)
        ActiveSheet.ListObjects("NewTable" & i).Resize rng
        nxt = nxt + rw + 2
    Next
    Application.ScreenUpdating = True
  
End Sub

I hope this helps.

 

[mdwasim]

Thank you for the efforts iGold!,
This is for sure going to be helpful, never mind about the grouping thing, those are for consolidating records into single table instead of making more tables.
For now, what you have given is excellent, I will tweak or use your code to match my requirement. In case need any help for sure will post in forum.

Cheers!