Macro to add the table based on cell value

[Vishalunleashed]

Hi Team,

I am looking for a code that adds the table based on the cell value. Let's say in column f2 I have added 10 and then if I run the macro it should insert a table of 10 rows. I have come up with below code but I am not able to add user defined value:

Sub Macro1()
    Application.CutCopyMode = False
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$L$4"), , xlYes).Name = "Table1"
    Range("Table1[[#All],[Column1]]").Select
    ActiveSheet.ListObjects("Table1").Resize Range("$L$4:$L$14")
    Range("F2").Select
End Sub

 

[Stevie]

Hi Vishal,
I don't want to go rewriting all your code, so the simplest change to make your code do what is described is:

Sub Macro1()
    Application.CutCopyMode = False
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$L$4"), , xlYes).Name = "Table1"
    ActiveSheet.ListObjects("Table1").Resize  ActiveSheet.ListObjects("Table1").range.Resize(Range("F2").value)
End Sub

I removed the line:

Range("Table1[[#All],[Column1]]").Select

as it caused an error for me.

I would very much suggest considering what you want to happen if you want to make more than one table, as this will currently cause an error as they would both be called 'table1'.

Furthermore consider what you want to happen if the table isn't allways in the same place, or if the value entered is not in the same place, or if you want to also be able to define the number of columns.

Also, I would steer clear of using 'Activeworkbook' and ' range.' where possible, but that might be my preference of being as explicit and verbose as possible.

Finally, I would suggest storing some items in variables, such as your table and ranges as this will make the macro easier to re-use.

Either way I hope this helped and answers your question.

If this was helpful, please click 'Like'!
Stevie

 

[Vishalunleashed]

Thank you so much Steve!

Appreciate your prompt response! The solution offered by you; worked like a charm.

Thanks once again

 

[Vishalunleashed]

Hi Steve & Team,

I am in the process of creating a macro which will act as a database wherein the user can add the number of rows that one needs to scan and it will be added in another tab which will act as a database. There is certain information that I would like to iterate it multiple times based on # of devices. For instance number of devices is 7 then it should add that details 7 times. The macro runs fine the first time but when I rerun it again with new values then it replaces the data which is previously added.

Following is the code and I am also attaching the excel macro.

Sub Macro_DB()

Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("TVI#").Select
Range("A1").Select
Selection.End(xlDown).Select
Range("A1590").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B1590").Select
ActiveWindow.SmallScroll Down:=12
Sheets("Input").Select
Range("C4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("TVI#").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Selection.End(xlToLeft).Select
Selection.End(xlDown).Select
Range("B1594:G1594").Select
Range(Selection, Selection.End(xlUp)).Select
Application.CutCopyMode = False
Selection.FillDown
Range("A1594").Select
Selection.End(xlUp).Select
Range("A2").Select
Sheets("Input").Select
Range("L2").Select
Selection.Copy
Range("L3:L12").Select
ActiveSheet.Paste
Columns("M:M").Select
Selection.EntireColumn.Hidden = True
Range("L6").Select
Application.CutCopyMode = False
Range("C3").Select
End Sub

I would appreciate your help on this.

Kind Regards,
Vishal

 

[Vishalunleashed]

Hi Everyone,

A gentle reminder on below query!

Thank you!

 

[Stevie]

The macro runs fine the first time but when I rerun it again with new values then it replaces the data which is previously added.

Hi Vishal three things;

1: This appears to be a new issue and should therefore be a new thread.
2: Please try to solve your problem before posting, and please post your attempted solution.
3: If you want to include code, please use the code tags, or click the

 __
/  |
|<>|
''''

button up on the left when writing a post to enter code so that others can read it easily.

Stevie

 

[Vishalunleashed]

Thank you Stevie, going forward I will upload a new issue. So do you want me to upload this as new issue? I have added the code as well