Copy Table Then Create Named Ranges

[tanyamc]

Is there a way to automate the following steps (automation help areas in bold):

Copy a table to a new worksheet which is automatically named based on either a user input or the date (month) of a source file (Power Query being used to join two files to create the source table)
Rename a column in that new worksheet to the worksheet name/month/user input and give that column's data a name in name manager (month$)
Pull in named columns from previous month(s) worksheets to the new worksheet in date order (July, June, May, etc.)

Background:
The goal is to create a worklist from a PQ combo of two files that are overwritten monthly. The PQ will create the worklist, but then it needs to be moved/unlinked so it's not overwritten the next month. It needs to have historical info added to it for each item.

Not all items will appear on a worklist every month, and brand new items can be added that won't have historical data. (Example: May will have 40 items out of the 400 possible, June will have 30 of the same items and 20 other items from the 400 in may, plus 10 items added since, from now 410 possible items.)

Open to suggestions of a better way to do all of it, as I am trying to improve a current process, which can sometimes create 'blinders' versus starting from scratch!

 

[tanyamc]

Sub CopyWorklist()

'Step 1: Tell Excel what to do if error

On Error GoTo MyError

'Step 2: Add a sheet and name it based on current month

Dim wsSource As Worksheet

Dim wsNewSht As Worksheet

Dim strMon As String



strMon = WorksheetFunction.Text(Now(), "mmm")



Sheets.Add After:=Worksheets("Info")

ActiveSheet.Name = strMon



Set wsSource = Worksheets("SourceSheet")

'Worksheet where Table I want to copy resides

'Assign new sheet to a worksheet variable



Set wsNewSht = Worksheets(strMon)



wsSource.Range("SourceTableName[#All]").Copy

wsNewSht.Range("A1").PasteSpecial xlPasteValues

Exit Sub

'Step 3: If here, an error happened, provide details to user

MyError:

MsgBox Err.Description, vbCritical, "Error Description"



End Sub

 

[tanyamc]

I was able to work out the first part. If you can help with the rest, please share. Thanks.