Insert rows from a master sheet, in between 2 named ranges using VBA

[Youngdand]

Hi,

I currently have a template in which i have a lot of pre prepared formula, which extract information from data in order to produce, invoices statements and remittance advice notes. All formula are written to use defined names, I have a range in which i currently insert data manually, and the formula works out the column totals by looking at anything in the cell header + 1 and total -1, meaning the range can grow and shrink dynamically. excat formula:

eg =ROUND(SUM(INDIRECT(ADDRESS(ROW(APHeader)+1,COLUMN(APHeader))):INDIRECT(ADDRESS(ROW(APtotal)-1,COLUMN(APtotal)))),2)

The sheet uses a Validation list, which pulls in all the data related to a particular client using lookups, that are required for the various output sheets, ie invoice, etc.

I am looking for a way, if possible to return all the data rows related to the client chosen, from the master list, and insert them in to the data sheet between the points apheader and aptotal without overwriting any of the items below aptotal. however the number of lines can vary depending on client.

Alternatively, a VBA script that can be linked to a button which looks up the value of named range client in the data sheet, and select the data from the master sheet with the corresponding client name, and copy the entire rows, relating to that client, and insert them in to the data sheet between position apheader and aptotal. some of these client may return numbers of rows in excess of 4K.

My VBA knowledge is basic, but i can normally follow through the logic.

Any tips or help would be greatly appreciated.


Thanks Dan.

 

[Youngdand]

Hi Tony,

That is fantastic, and i cant thank you enough. Does exactly what i needed.

Cheers,

Dan.

 

[tonyyy]

You're very welcome, Dan. Glad it worked out...