Updating master from 3 shared workbooks

[Stoic Boonie]

I have 4 workbooks.
Books 1,2 and 3 will have the same format, and be used by different people and shared.
Book 4 will be used to collate data from the previous three and tables, charts etc created.

I figured out enough VBA over the weekend to do macros for Books 1 ,2 & 3:
- I have written a macro which generates sheets (with the dates as names) for each non-public holiday workday. I have created a list of dates in Sheet1, column A of each Book.
- I have created a macro which only makes visible the current day's worksheet.


NOW for the TRICKY bit For Book 4:

- Sheet names (as Dates) of Books 1 to 3 are listed in Column A, (identical to the list of Dates used to generate the w-sheets in Books 1 to 3).
- Columns C to Q will be updated with the totals for various (columns) from Sheets 1 to 3). So each row contains the totals from the worksheet with the same name (as date) in Sheets 1,2 and 3.

Whenever Workbook 4 is opened I want it to be updated with data from
the Books 1 to 3 worksheets.

- It will search for previously non-updated sheets prior to current date (i.e. the end date) and after the previous update. This start date could be set by returning the current search end Date to a cell (eg “P1”), and then referring to it as the start date during the macro. To avoid updating previously updated Sheets, I thought to move the date value from column A into column B (column A can be hidden so only the sheet-names which have been utilised will be shifted to column B).
- Can it use column A as w-sheet names (formatted as date) to find the w-sheets with the same names in the other books?
- It will copy cell data (H53 to L53) (note, these cells are formulas: summed column data) from the Sheets of Books 1 to 3, and paste in the corresponding row in columns C to G (for Book1), H to L (for Book 2), and M to Q (for Book 3)
- Books 1, 2 & 3 w-sheets will be hidden, and the bookds shared: is that a problem?

I have not included my macro here, because my attempts were reaping numerous errors that I could not interpret.

If anybody could code this out, or pass on some ways to go about this, I would be most greatfull. I've spent hours on this only to get repeatedly stumped (or hit for a 6).

A Boonie Fan

[Tom Ogilvy]

I assume if a cell in column 3 (C) has been processed, it will have a value
in it. So find the last row that has a value in it. To find the last date,
do the same in column a (A)

So this assumes there is no data below the date data in column1 or the
status data in column 3.

The workbooks only need to be open. They don't need to have their sheets
visible except it assumes the summary sheet Book4 is the active sheet. .

Dim rng as Range, rng1 as Range, rng2 as Range
dim bk1 as Workbook, bk2 as Workbook, bk3 as Workbook
Dim sh1 as Worksheet, sh2 as Worksheet, sh3 as Worksheet
Dim cell as Range
set rng = cells(rows.count,3).End(xlup).offset(0,-2)
set rng1 = cells(rows.count,1).End(xlup)
set rng2 = range(rng,rng1)
set bk1 = Workbooks("Book1.xls")
set bk2 = Workbooks("Book2.xls")
set bk3 = Workbooks("Book3.xls")
for each cell in rng
set sh1 = bk1.Worksheets(cell.Text)
set sh2 = bk2.Worksheets(cell.Text)
set sh3 = bk3.Worksheets(cell.text)
cell.offset(0,i+1).Resize(1,5).Value = _
sh1.Range("H53").Offset(0,i-1).Resize(1,5).Value
cell.offset(0,i+6).Resize(1,5).Value = _
sh2.Range("H53").Offset(0,i-1).Resize(1,5).Value
cell.offset(0,i+11).Resize(1,5).Value = _
sh3.Range("H53").Offset(0,i-1).Resize(1,5).Value
Next

--
Regards,
Tom Ogilvy


"Stoic Boonie" <Stoic.Boonie.1zjrey_1133752502.0423@excelforum-nospam.com>
wrote in message
news:Stoic.Boonie.1zjrey_1133752502.0423@excelforum-nospam.com...
>
> I have 4 workbooks.
> Books 1,2 and 3 will have the same format, and be used by different
> people and shared.
> Book 4 will be used to collate data from the previous three and tables,
> charts etc created.
>
> I figured out enough VBA over the weekend to do macros for Books 1 ,2 &
> 3:
> - I have written a macro which generates sheets (with the dates as
> names) for each non-public holiday workday. I have created a list of
> dates in Sheet1, column A of each Book.
> - I have created a macro which only makes visible the current day's
> worksheet.
>
>
> NOW for the TRICKY bit For Book 4:
>
> - Sheet names (as Dates) of Books 1 to 3 are listed in Column A,
> (identical to the list of Dates used to generate the w-sheets in Books
> 1 to 3).
> - Columns C to Q will be updated with the totals for various (columns)
> from Sheets 1 to 3). So each row contains the totals from the
> worksheet with the same name (as date) in Sheets 1,2 and 3.
>
> Whenever Workbook 4 is opened I want it to be updated with data from
> the Books 1 to 3 worksheets.
>
> - It will search for previously non-updated sheets prior to current
> date (i.e. the end date) and after the previous update. This start date
> could be set by returning the current search end Date to a cell (eg
> "P1"), and then referring to it as the start date during the macro. To
> avoid updating previously updated Sheets, I thought to move the date
> value from column A into column B (column A can be hidden so only the
> sheet-names which have been utilised will be shifted to column B).
> - Can it use column A as w-sheet names (formatted as date) to find the
> w-sheets with the same names in the other books?
> - It will copy cell data (H53 to L53) (note, these cells are formulas:
> summed column data) from the Sheets of Books 1 to 3, and paste in the
> corresponding row in columns C to G (for Book1), H to L (for Book 2),
> and M to Q (for Book 3)
> - Books 1, 2 & 3 w-sheets will be hidden, and the bookds shared: is
> that a problem?
>
> I have not included my macro here, because my attempts were reaping
> numerous errors that I could not interpret.
>
> If anybody could code this out, or pass on some ways to go about this,
> I would be most greatfull. I've spent hours on this only to get
> repeatedly stumped (or hit for a 6).
>
> A Boonie Fan
>
>
> --
> Stoic Boonie
> ------------------------------------------------------------------------
> Stoic Boonie's Profile:
http://www.excelforum.com/member.php...o&userid=29356
> View this thread: http://www.excelforum.com/showthread...hreadid=490645
>