Excel Tables - how to update?

[Graemea]

Hi,

i have a simple workbook that consists of 6 worksheets, 5 of which are populated with reports that are pasted in from an Excel extract from 3rd party software, and the other one uses VLOOKUPs to extract and consolidate the data from the data sheets.

I thought that it might be a good idea to convert the data in the 5 data sheets into Tables (Ctrl+T). However, the data in these sheets is updated each month by pasting over new data, which may contain more (or fewer) columns and rows than the previous month.

Are Excel Tables able to accommodate (e.g. expand/contract) pasting over with new data or would I need to paste in the data each month and then convert the ranges to Tables?

Thanks!

 

[CA_Punit]

Tables will automatically update. A message will appear as you paste data which is more than the previous data.

 

[Graemea]

Thanks.

I did a quick test and it seems to work when I paste over more data than was there before (the Table range automatically expands, though no message appeared) but the Table range doesn't contract when I paste over less data than was there before; the extra data seems to remain?

 

[CA_Punit]

Table range doesn't contract when I paste over less data than was there before; the extra data seems to remain?

Thats an issue.

 

[Graemea]

Does that mean that this is a known problem with Tables?

 

[CA_Punit]

If there is excess data in table and if you paste less rows or column of data it wont clear the excess itself. You have to first clear the data and then paste.

VBA might help i suppose to remove all the data before you paste it.!!!!