Macro to Copy Dynamic Range from Table and Paste Values into a Table on a Different Sheet

Using Excel 2016

I want to take information from a table on a different tab and paste the values into the table I want to sort.

I know it is easy enough to do this manually, but I'm creating this macro button to ease the functionality as this sheet will be used by someone who is not too excel-savvy. The table is currently populated by formulas, referring to a table on a different sheet, which is therefore preventing me from using filters/slicers to sort the information. If anyone knows formulas I can use in my table to allow sorting please share, however I believe this is only possible with static, locked-down cell references which won't work for this table.

Details:
1. Source table is named "PT" and resides on Sheet1(Proposals), destination table is named "ST" and resides on Sheet3(Summary)
2. The source table I want to copy information from will regularly have rows added in, so the range selection needs to be dynamic (currently the range of data I want to copy is C19:G89, full table is currently B19:H89)
3. I only want to copy a certain set of columns from the source table (C:G, column headers are (C)Project>(D)Category>(E)Vendor>(F)Proposal>(G)Task)
4. The area in the destination table where I want to paste the values into is columns "B:F" (column headers are (B)Project>(C)Category>(D)Vendor>(E)Proposal>(F)Task, currently the area is B19:F89)

The spreadsheet is attached. FYI, this is a work in progress and some things are not yet finalized.

Hello nickt94,

I would do something like this to use the power of the listobjects you already created. Tested and works fine with your data.

Does that work for you?

Please Login or Register  to view this content.