Have a newly created template sheet with table automatically append mater table. Building a fully automated template, hung up on the PowerQuery

Question

The Project Premise:

Creating a invoice tracking template that automatically appends a table on the newly created invoice worksheet to an existing table on the Master Worksheet.

The process will work as follows:

Existing: Invoice Template, Master (button to create new worksheet)
User clicks button to pull up a user form
form collects information
macro duplicates "Invoice Template" and fills in information that was entered into the form

-----------COMPLETE TO THIS POINT-----------------

macro renames the table(listObject) on the new worksheet to match information collected from the form
macro appends the renamed table to the master table on "Master" Worksheet (adding a column with the source table's name)
macro then refreshes data and changes the color of the "worksheet tab" based on data from form

The information from the form is conditional and data collection is not the issue. I have not been able to get the table to rename, thus cannot continue programming the rest of the process.

I have uploaded the file for download here:

file was removed

My progress on the project:

Existing: Invoice Template, Master (button to create new worksheet)
User clicks button to pull up a user form
form collects information
macro duplicates "Invoice Template" and fills in information that was entered into the form

-----------COMPLETE TO THIS POINT-----------------

The current step to require a working solution:

5. macro renames the table(listObject) on the new worksheet to match information collected from the form

I have tried this (see previous question):

Finding The Table With The Most Recent Creation Date

However, The tables will not have sequential names and names may contain text of different links.

In Reply to Tim:


Sub DuplicateAndAppendTable(projName, ctrNum, taskNum, activDes, venName, grantAcc, locAcc, grantAmt, matchPercent)
    contractType.Hide
    Dim ws0 As Worksheet, ws1 As Worksheet, ws2 As Worksheet
    Dim tabName As String
    
    Dim oListObj As ListObject
     
    ' Set references to the worksheets
    Set ws0 = ThisWorkbook.Sheets("Sheet0")
    Set ws1 = ThisWorkbook.Sheets("projectMaster")
    
    'Copy Sheet0
    ws0.Copy After:=Worksheets(Sheets.Count)
    
    'Conditional to create new sheet name
    If taskNum = "N/A" Then
    tabName = Replace(ctrNum, "-", "") & "-" & activDes
    Else
    tabName = Replace(ctrNum, "-", "") & "-" & taskNum & "-" & activDes
    End If
    
    'Rename sheet0 copy, create var ref  wsn (worksheet new)
    ActiveSheet.Name = tabName
    Set wsn = ThisWorkbook.Sheets(tabName)
    
    'grant
    wsn.Range("N12").Value = grantAcc
    wsn.Range("M12").Value = locAcc
    wsn.Range("N14").Value = grantAmt
    wsn.Range("N13").Value = matchPercent
    
    'project info
    wsn.Range("D1").Value = projName
    wsn.Range("D2").Value = venName
    wsn.Range("D4").Value = ctrNum
    wsn.Range("I1").Value = activDes
    wsn.Range("D5").Value = taskNum
    wsn.Range("B1").Value = Date
    
    
    Set wsn.ListObjects(1).Name = "TEST"
    
End Sub

This returns Run-time error '424': Object Required

Since you control the creation of all new listobjects, why not track them on a hidden sheet? Then you can definitively know which one was last-created... — Tim Williams, Aug 17 '23 at 19:41
Please provide enough code so others can better understand or reproduce the problem. — Community, Aug 18 '23 at 15:13
Thank you Tim! I like the simplicity of your idea, but it would a be much cleaner product if it were handled in the .vba — Phentius Khan, Aug 25 '23 at 21:02
Tim, see the addition to the question regarding #5. I apologize for late reply. -_- this is silly. I removed the "Set" and it works perfectly. — Phentius Khan, Aug 25 '23 at 21:41

Phentius Khan · Answer 1 · 2023-08-30T13:07:25.377

Sub DuplicateAndAppendTable(projName, ctrNum, taskNum, activDes, venName, grantAcc, locAcc, grantAmt, matchPercent)
contractType.Hide
Dim ws0 As Worksheet, ws1 As Worksheet, ws2 As Worksheet
Dim tabName As String

Dim oListObj As ListObject
 
' Set references to the worksheets
Set ws0 = ThisWorkbook.Sheets("Sheet0")
Set ws1 = ThisWorkbook.Sheets("projectMaster")

'Copy Sheet0
ws0.Copy After:=Worksheets(Sheets.Count)

'Conditional to create new sheet name
If taskNum = "N/A" Then
tabName = Replace(ctrNum, "-", "") & "-" & activDes
Else
tabName = Replace(ctrNum, "-", "") & "-" & taskNum & "-" & activDes
End If

'Rename sheet0 copy, create var ref  wsn (worksheet new)
ActiveSheet.Name = tabName
Set wsn = ThisWorkbook.Sheets(tabName)

'grant
wsn.Range("N12").Value = grantAcc
wsn.Range("M12").Value = locAcc
wsn.Range("N14").Value = grantAmt
wsn.Range("N13").Value = matchPercent

'project info
wsn.Range("D1").Value = projName
wsn.Range("D2").Value = venName
wsn.Range("D4").Value = ctrNum
wsn.Range("I1").Value = activDes
wsn.Range("D5").Value = taskNum
wsn.Range("B1").Value = Date


'Set wsn.ListObjects(1).Name = "TEST" (REMOVE "Set")
wsn.ListObjects(1).Name = "TEST"

End Sub

Have a newly created template sheet with table automatically append mater table. Building a fully automated template, hung up on the PowerQuery

1 Answers1