Create a table after performing a action, VBA

[mduntley]

Hello,

I have a VBA code that I had made, but having a difficult time making a table from it. It has to be from a current selection, instead of A5 as it is never in the same cell in each workbook. Can someone help me?

Sub template()
'
' template Macro
'


'
ActiveCell.Select
ActiveCell.FormulaR1C1 = "word"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "Date and Time:"
ActiveCell.Offset(1, -1).Select
ActiveCell.FormulaR1C1 = "Start Time:"
ActiveCell.Offset(0, 1).Select
Selection.NumberFormat = "d/m/yyyy h:mm AM/PM"
ActiveCell.Offset(1, -1).Select
ActiveCell.FormulaR1C1 = "Build Complete:"
ActiveCell.Offset(0, 1).Select
Selection.NumberFormat = "d/m/yyyy h:mm AM/PM"
ActiveCell.Offset(1, -1).Select
ActiveCell.FormulaR1C1 = "Updated:"
ActiveCell.Offset(0, 1).Select
Selection.NumberFormat = "d/m/yyyy h:mm AM/PM"
ActiveCell.Offset(1, -1).Select
ActiveCell.FormulaR1C1 = "Ticket:"
ActiveCell.Offset(0, 1).Select
Selection.NumberFormat = "d/m/yyyy h:mm AM/PM"
ActiveCell.Offset(1, -1).Select
ActiveCell.FormulaR1C1 = "Send To:"
ActiveCell.Offset(0, 1).Select
Selection.NumberFormat = "d/m/yyyy h:mm AM/PM"
ActiveCell.Offset(1, -1).Select
ActiveCell.FormulaR1C1 = "Completed:"
ActiveCell.Offset(0, 1).Select
Selection.NumberFormat = "d/m/yyyy h:mm AM/PM"
ActiveCell.Offset(1, -1).Select
ActiveCell.FormulaR1C1 = "Status:"
ActiveCell.Offset(0, 1).Select
ActiveCell.Offset(-7, -1).Select
Selection.Offset(1, 2).Select
End Sub

 

[My Aswer Is This]

Try this:

Sub New_Sub()
'Modified  10/19/2018  5:54:56 AM  EDT
Application.ScreenUpdating = False
Dim tbl As ListObjects
Dim x As Long
For Each tbs In ActiveSheet.ListObjects
x = x + 1
Next
With ActiveCell
.Value = "word"
.Offset(1).Value = "Start Time"
.Offset(2).Value = "Build Complete:"
.Offset(3).Value = "Updated:"
.Offset(4).Value = "Ticket:"
.Offset(5).Value = "Send To:"
.Offset(6).Value = "Completed:"
.Offset(7).Value = "Status:"
.Offset(, 1).Value = "Date and Time:"
End With
ActiveCell.Resize(8, 2).Select
ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes).Name = "Table" & x + 1
Selection.Columns.AutoFit
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

I made a mistake and forgot about the formatting.
Try this:

Sub New_Sub_Mod()
'Modified  10/19/2018  6:38:56 AM  EDT
Application.ScreenUpdating = False
Dim tbl As ListObjects
Dim x As Long
For Each tbs In ActiveSheet.ListObjects
x = x + 1
Next
With ActiveCell
.Value = "word"
.Offset(1).Value = "Start Time"
.Offset(1, 1).NumberFormat = "d/m/yyyy h:mm AM/PM"
.Offset(2).Value = "Build Complete:"
.Offset(2, 1).NumberFormat = "d/m/yyyy h:mm AM/PM"
.Offset(3).Value = "Updated:"
.Offset(3, 1).NumberFormat = "d/m/yyyy h:mm AM/PM"
.Offset(4).Value = "Ticket:"
.Offset(4, 1).NumberFormat = "d/m/yyyy h:mm AM/PM"
.Offset(5).Value = "Send To:"
.Offset(5, 1).NumberFormat = "d/m/yyyy h:mm AM/PM"
.Offset(6).Value = "Completed:"
.Offset(6, 1).NumberFormat = "d/m/yyyy h:mm AM/PM"
.Offset(7).Value = "Status:"
.Offset(, 1).Value = "Date and Time:"
End With
ActiveCell.Resize(8, 2).Select
ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes).Name = "Table" & x + 1
Selection.Columns.AutoFit
Application.ScreenUpdating = True
End Sub

 

[mduntley]

How do i add something to this, such as I am trying to do this after

.Offset(, 1).Value = "Date and Time:"
.Offset(1, 1).ActiveCell.Value = Now
End With

 

[My Aswer Is This]

You never said if it even does what you want.
Most posters will say that did what I want but can you add this:

Try this:

[LEFT][COLOR=#222222][FONT=Tahoma]Sub New_Sub_Mod()[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma]'Modified  10/19/2018  7:05 AM  EDT[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma]Application.ScreenUpdating = False[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma]Dim tbl As ListObjects[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma]Dim x As Long[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma]For Each tbs In ActiveSheet.ListObjects[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma]x = x + 1[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma]Next[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma]With ActiveCell[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma].Value = "word"[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma].Offset(1).Value = "Start Time"[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma].Offset(1, 1).NumberFormat = "d/m/yyyy h:mm AM/PM"[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma].Offset(1, 1).Value = Now()[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma].Offset(2).Value = "Build Complete:"[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma].Offset(2, 1).NumberFormat = "d/m/yyyy h:mm AM/PM"[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma].Offset(3).Value = "Updated:"[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma].Offset(3, 1).NumberFormat = "d/m/yyyy h:mm AM/PM"[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma].Offset(4).Value = "Ticket:"[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma].Offset(4, 1).NumberFormat = "d/m/yyyy h:mm AM/PM"[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma].Offset(5).Value = "Send To:"[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma].Offset(5, 1).NumberFormat = "d/m/yyyy h:mm AM/PM"[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma].Offset(6).Value = "Completed:"[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma].Offset(6, 1).NumberFormat = "d/m/yyyy h:mm AM/PM"[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma].Offset(7).Value = "Status:"[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma].Offset(, 1).Value = "Date and Time:"[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma]End With[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma]ActiveCell.Resize(8, 2).Select[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma]ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes).Name = "Table" & x + 1[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma]Selection.Columns.AutoFit[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma]Application.ScreenUpdating = True[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma]End Sub[/FONT][/COLOR][/LEFT]

 

[mduntley]

No you did what i wanted. I am trying to learn VBA and trying to figure stuff out on my own beside i ask for help.

 

[My Aswer Is This]

Glad to see your learning Vba.

Glad I was able to help you.