Copy Table Then Create Named Ranges

tanyamc

New Member
Joined
Aug 24, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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!
 

Attachments

  • source.jpg
    source.jpg
    97.3 KB · Views: 4
  • destination.jpg
    destination.jpg
    112.7 KB · Views: 4

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

tanyamc

New Member
Joined
Aug 24, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 24, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
I was able to work out the first part. If you can help with the rest, please share. Thanks.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,697
Messages
5,765,989
Members
425,321
Latest member
stefanov07

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top