Combine data from different sheets in Book1 into new sheet in Book2 whne run macro

Hi,

The approach I'd take is to create a macro in Book 1 to be run on each sheet which would
1. For each row on the sheet count the number of values in G:I
2. Copy cells A:F and paste to as many new rows as the count indicates on a new summary sheet at the next blank row
3. For each new row add the G/H/I/J value (if not blank) to column G in the summary
4. For each new row add the K/L/M/N value (if not blank) to column H in the summary

Originally Posted by MayDay1988

Hi Richard, thank you for your suggestion. My actual book1 is locate in share drive but I don't anybody to acess the file so I keep my book2 in local drive. Is there a way to do macro in book2?

If you don't wan't anyone to access the file why not password protect the workbook so that it can't be opened?

So you're saying that even you aren't allowed to access the shared drive?

Why not then simply have your local workbook 2 run a macro that opens the file in book 1 which contains the macro I suggested and then run that macro?

As an aside it seems somewhat ironical that you have a shared drive that can't be 'shared' for this purpose.

However, and since you need some sort of external macro to access the file to do what you want to do, there seems nothing to prevent someone else simply writing a macro to delete any file in that shared folder.