Copy two data sets, filter it and paste to summary sheet

Hi Guys
I have the following Macro and wondered if anyone could help me adapt it?

Please Login or Register  to view this content.

This is what I need:
I need it to copy the customer reference and customer name on the “budget” tab which will always be in C6:D105.

Then I also need it to copy the customer reference and customer name from the “Act Unit” sheet. However the difference with this is that this data isn’t in a set range – I would need the macro to look up the last occurrence within a column if possible? Or even if it copied the entire rows (3&4 starting in column H then that would be ok).

Then I would need it to paste the data to the “Macro” sheet (the 2nd lot of data will need to be transposed).

Once pasted I need and auto filter applied for unique values and finally copied and pasted to the “Summary” sheet.


Can anyone advise where to start?

I have attached an example file

I get an error here:
Columns("D6:D105")

Previously when the macro worked for something else this was columns "D:D" how do I restrict it to cells D6:D105?

Regarding the copy column would this code be what i am after???: I am clutching at straws as I dont know VBA at all

HTML Code: 

Hi,
I managed to get the first part of the copy and past working but REALLY stuggling with the 2nd part.

Here is the code that copies the data on the budget sheet:

Please Login or Register  to view this content.

I now need to copy the data on “Act Units” sheet Row 4 starting in column H to the last non blank column. Can anyone help with this part please?

Would this select the data I need?:

Please Login or Register  to view this content.

Anyone? I'm completely stuck on how to do this

Hi Arlette, thanks for looking.

I managed to get that bit to work. What I am stuck on now is selecting the range for the data on the "act Units" sheet. This can be found starting in cell H4 but then the code would need to find the last column used. Can you help with this part?