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

Youngdand

Board Regular
Joined
Sep 29, 2017
Messages
120
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

Board Regular
Joined
Sep 29, 2017
Messages
120
Hi Tony,

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

Cheers,

Dan.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Forum statistics

Threads
1,137,113
Messages
5,679,701
Members
419,850
Latest member
Cbell07958

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top