Excel 2010 - Problems with auto copy from sheet to sheet

This is probably the most awesome thing I'll write this month.

Here you go!

I'll follow up with another post describing what I did, to the best of my ability.

Copy of Combined%20Project%201(1).xlsm

Here's the Visual Basic code I put together that does everything:

Please Login or Register  to view this content.

And a second piece of code..

Please Login or Register  to view this content.

First I designed a standard Advanced Filter Macro.

Advanced Filtering works by comparing a table of data (that has headers) to a set of criteria, which is just a list of those same headers with something below it in the next row. In your case, we want to compare the table to Room Number and Two.

First, I added an extra tab with your table headers at the top. In the row right beneath Room Number I put ='Report - Room'!G2 so that it autopopulates with the value you want to filter against. A1:I2 represents the filter.

Next, I used the following code to look at your raw data, compare it to the Filter criteria I've designed, and then instantly paste it to A4 right below the filter.
This is the heart of every basic advanced filter macro.

Please Login or Register  to view this content.

The reason for this, is that its hard to paste the data as it is into merged cells, so this is sort of a baby step in between.

The second half of the code finds where the last row of data is. Now it knows Row 5 to ??? is what it cares about. Now it copies the first cell from column B into A5:A8, which has to be specifically spelled out because of the merged cell. It adds 4 to the row numbers, and pastes the next value into A9:A12 and so on.

It then repeats the same process for each row it found down Column C and then down Column D.


In the beginning of this subroutine, I put Application.Updating = False which tells Excel to stop updating while this is running, otherwise you'd see Excel jump around as it looks at different tabs and pastes into each cell. This is followed by a clear command, which clears your form and filter tab so that you have fresh results every time. At the very end of the whole thing, we tell Excel to turn the ScreenUpdating back on.



The second code block I wrote it a Worksheet Change Event. This type of code goes directly onto a worksheet's VB page. It states that if something I declare changes, to run. In my code, I said if G2 (the dropdown on Report - Rooms) changes, run the Filtering and Pasting macro.

So, now every time you change the dropdown it will run through all of that code all over again.

Hi and welcome to the forum

Here is another option if you dont want to/cant use VBA. It uses a helper column on Master sheet (I used J, you can use whatever you want, just change the references inthe nexy formula), with this , copied down...
=IF(A2='Report - Room'!$G$2,COUNTIF('Master INFO'!$A$2:A2,'Master INFO'!A2),"")

Then, make sure the headings on each sheet are the same (change Project No: to Project No, or vise versa)
=IFERROR(INDEX('Master INFO'!$A:$I,MATCH((ROW()-1)/4,'Master INFO'!$J:$J,0),MATCH('Report - Room'!A$3,'Master INFO'!$A$1:$I$1,0)),"")

Edit: Sorry daffodil, that looks like an awesome job you did there, my VBA sucks, so I have become a formula jockey lol

Thanks for the edit, I feel better now.

I'm rather bad at it myself but I felt like tackling a challenge by trying to circumvent VBs limitation on pasting into merged cells.

It was 90% franken-coding and 10% ingenuity. I'll get around to really learning it eventually.

Assmuming the layout and tabs remain the same, nothing needs to be changed. All you would need to do is to copy the headers from your actual into A1 of the Filter tab, and then change the value directly below that first header to target the dropdown's cell.

If tab names changes, this can be easily modified in the code.

Copy this into J2, and then copy down as far as you need it to go...
=IF(A2='Report - Room'!$G$2,COUNTIF('Master INFO'!$A$2:A2,'Master INFO'!A2),"")

For this to work, you need to make sure the headings on each sheet are the same. For instance, on sheet 1 you have "Project No:" but on sheet 2, you only have "Project No"...make them the same

Then, where you want the answers to be, copy this, and then put this in A5, and copy down and across
=IFERROR(INDEX('Master INFO'!$A:$I,MATCH((ROW()-1)/4,'Master INFO'!$J:$J,0),MATCH('Report - Room'!A$3,'Master INFO'!$A$1:$I$1,0)),"")[/QUOTE]