Insert rows when data set is extended

[Busymanjohn]

Hi All, I am looking for a solution to a small problem ( and I am sure I have come across the solution before but cannot find I ). I have a colleague who has a data set in sheet 1 with a pull through of the data set in sheet 2. The problem is that the pull through in sheet 2 is set to 5 rows of data and the data set can extend beyond that, now rather than having to go in and manually add rows in sheet 2 every time the data set is extended, the solution I am looking for is to automatically extend the pull through when the data set goes beyond say 5 rows of data ... is that possible?

 

[Busymanjohn]

Sample file attached for clarity.

 

[PCosta87]

Hi,

Have you tried using a Pivot Table for this... convert the range in sheet 1 to an Excel Table and create a Pivot Table from that in sheet 2.

You can then refresh the PT and all data will be pulled from the source table.

Please refer to attachment,
Hope this helps

 

[Busymanjohn]

Hi PCosta87, thanks for the reply, a PT wouldn't be ideal as the pull through has to be in a certain format, with headers and some extra data as this is used as a form for a work order, but nice idea.

 

[Nebu]

Hi:

Convert your data range into a table and add total row using table design tab.

Thanks

 

[Busymanjohn]

Hi Nebu, not quite what i am looking for, i want the table to update ( add a row ) each time the raw data is updated with new info ,,,, i.e. if the raw data has 12 rows of data then have the table auto update to 12 rows of data instead of having to add rows manually each time.

 

[Nebu]

Hi:

Just drag the formulas down to how many rows you want. The total will automatically update.If this is not what you want you may need VBA to do what you want, but in that case you should know how many rows you want to update before hand to feed that into the macro to update that many rows automatically.

Thanks

 

[Busymanjohn]

Hi Nebu, yeah, I knew it would involve VBA ( i;m not good with VBA ) and had hoped someone would have the answer. Thanks


▬▬▬▬▬▬▬▬▬ Mod edit :

if you really knew that better & faster was to post in VBA forum ‼​

▬▬▬▬▬▬▬▬▬ thread moved to appropriate forum !

​