Relative Sheet and Table names in VBA

[Davidns]

I used the macro recorder to create a script shown below. The problem is that I need the sheet name reference to be the name of the sheet that I am in when running the script. Is this possible?
Also, the script refers to a table, which i also need to reference the table name of the table I am currently in. Is this possible as well?
In other words, both the sheet name and table name need to be dynamic rather than fixed, and I just don't know how to go about that. Thanks in advance!

Application.Goto Reference:="pt"
Selection.Copy
Sheets("Sheet1").Select
Range("Table7[[#Headers],[Date]]").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Columns("A:A").EntireColumn.AutoFit

 

[tlowry]

Take a look at this. I think it does for which you asked.

Sub test()
    FormatDate "Sheet1", "Table1", Range("A4")
End Sub
Sub FormatDate(shtName, tblName, cCell)
    Application.ScreenUpdating = False
    cCell.Cells(1, 1).Copy
    With Sheets(shtName).Range(tblName & "[[#Headers],[Date]]")
        .PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
                SkipBlanks:=False, Transpose:=False
        .EntireColumn.AutoFit
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[Davidns]

Thanks but for some reason it is not working. Is there perhaps a variable like activesheet or activetable that i could use instead of a sheet or table name and if so, what is the propert syntax? Thanks!

 

[tlowry]

Oh, you need documentation.

Put all the code in a module
Change the line:

[B]FormatDate "Sheet1", "Table1", Range("A4")[/B]

• "Sheet1" to the name of the sheet with the table
  • If you want you can use Activesheet.name
• "Table1" name of the table
• "A4" a cell which has the format for the Date header you want.

<the name="" of="" the="" sheet="" with="" table="" in="" it=""><the name="" of="" the="" table=""><the cell="" that="" is="" formatted="" as="" you="" want="" the="" table="" header="" to="" be="">

Sub test()
   [B] FormatDate "Sheet1", "Table1", Range("A4")[/B]
End Sub
Sub FormatDate(shtName, tblName, cCell)
    Application.ScreenUpdating = False
    cCell.Cells(1, 1).Copy
    With Sheets(shtName).Range(tblName & "[[#Headers],[Date]]")
        .PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
                SkipBlanks:=False, Transpose:=False
        .EntireColumn.AutoFit
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

</the></the></the>

 

[My Aswer Is This]

Assuming all your tables have the header Date in column 5 of the table
Try this:

Sub Active_Table()
'Modified 4-22-18 8:10 AM EDT
On Error GoTo M
Dim tn As String
tn = ActiveCell.ListObject.Name
Range("pt").Copy
ActiveSheet.ListObjects(tn).HeaderRowRange(5).PasteSpecial Paste:=xlPasteFormats
Columns("A:A").EntireColumn.AutoFit
Application.CutCopyMode = False
Exit Sub
M:
MsgBox "Your active cell is not inside a table range"
Application.CutCopyMode = False
End Sub

 

[My Aswer Is This]

Or if your value Date in header row is not always in Table header row column 5:
And I'm assuming Date means the Value "Date" and not todays Date

Try this:

Sub Active_Table()
'Modified 4-22-18 9:25 AM EDT
On Error GoTo M
Dim tn As String
Dim c As Range
tn = ActiveCell.ListObject.Name
    For Each c In ActiveSheet.ListObjects(tn).HeaderRowRange
        If c.Value = "Date" Then
        Range("pt").Copy
        c.PasteSpecial Paste:=xlPasteFormats
        Columns("A:A").EntireColumn.AutoFit
        Application.CutCopyMode = False
        Exit Sub
        End If
    Next
    Exit Sub
M:
MsgBox "Your active cell is not inside a table range"
Application.CutCopyMode = False
End Sub