VBA that pastes from data dump and formats any excess cells depending on how man cells

[Bisky]

Hi there,

I wondering if someone can help me with a code that copy and pastes the data into the other sheets matching column. However as with doing this is there a way to reduce or extend the formatting of the table in the first sheet.

I've attached a crude example that only has a new dump with only 8 rows , thus the full table should be ruduced to the corresponding row in the on the new sheet or if there is more project numbers that the table is increased to match the amount.

Basically I have a sheet that runs off VLOOKUPS that use the project ID number however it runs infinitely down the column as =queryA:A, and I want a VBA code that can copy the amount of ID numbers everytime the query is updated and changed/reduced whilst also copying the VLOOKUP Formulas or up or down if necessary.

Any help would be hugely appreciated.

Thanks All

[dflak]

I don't understand the requirement. Are you looking for something to read the data on Query Dump and append it to Sheet 1?

[Bisky]

Sorry I will try explain,

Basically sheet 1 gets its Project ID(1st Column from the Query Dump 1st Column). Once this has been established every other column on sheet 1 runs a VLOOKUP from these numbers. My question is , is it possible to have a macro attached to a button that when clicked, can copy and paste the Project ID from the Query Dump into sheet 1 (1st Column) and then from that , determine if there is more or less numbers than the previous paste and thus extend/reduce the other columns formulas & Formatting of Sheet 1s Table.

[dflak]

OK, the way I am reading this is that you want to REPLACE the old data with the new.

[Bisky]

Yes, everytime I refresh the query dump there may be a greater or less amount of Project IDs. Instead of physically having to extend sheet1s table formatting/formulas.I was the code to run down the query dump and copy the amount of Project IDs then paste into sheet1. Then the code should either extend or reduce each column within the sheet1 table.See attached it basically shows the crude function of how my table uses VLOOKUP based upon the Project ID numbers.

[dflak]

This workbook makes use of Excel Tables. I converted the data range on the project sheets to an excel table. One of the features of an excel table is that it preserves all formatting, formulas, validations, etc. as data is copied in. Tables also know how big they are, so formulas and pivot tables that are built from them are dynamic as you add or delete data. You can also reference tables by their parts, such as the header, total row, data body or use column headers as parts of formulas or VB code.

Here is more information on tables: http://www.utteraccess.com/wiki/Tables_in_Excel.

Here is the code that does the magic. There isn't much to it.

Please Login or Register  to view this content.

The ClearTable function is in its own module. It is fairly standard code that I use all the time. It checks to see if there is a filter on the table and resets it, and then removes all table data.