Hi guys,

I'm looking for help with a sub* which can automate this process:

I've opened 3 books:
Summary_May2005.xls
11002404_abcdef.xls
11003702_abcdef.xls

In Summary.xls
there will be sheets named as: 11002404, 11003702, etc

These sheets are named with the project nos,
i.e. the first 8 digits of the books' filenames :
11002404_abcdef.xls
11003702_abcdef.xls

In each:
11002404_abcdef.xls
11003702_abcdef.xls

there will be a sheet named: Monthly Status Report

This sheet is the one which needs to be copied and pasted over as values
into
Summary_May2005.xls (into the corresponding sheets over there, overwriting
previous data.)

And if the sheet(s) to be copied/pasted over doesn't exist as yet in
Summary_May2005.xls (e.g.: new cases), the copy/paste will then be done in a
new sheet(s) created which will then be named after the first 8 digits of
the file(s).

*sub to run from Summary_May2005.xls

Grateful for any insights. Thanks.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Hi guys,

Am willing to pare down the specs substantially <g. I'm really not sure
which part of the post / process proved insurmountable. Hopeful for some
comments / experience to be thrown this way. Thanks.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Lightly tested but no error-checking provided!

Sub PasteNewInfo()
Dim wb As Workbook, wbSummary As Workbook
Set wbSummary = Workbooks("Summary_May2005.xls")
For Each wb In Workbooks
If wb.Name < wbSummary.Name Then
If Not WorksheetExists(Left(wb.Name, 8), wbSummary.Name) Then
wbSummary.Worksheets.Add.Name = Left(wb.Name, 8)
End If
wb.Worksheets("Monthly Status Report").Cells.Copy _
wbSummary.Worksheets(Left(wb.Name, 8)).Range("A1")
End If
Next
Application.CutCopyMode = False
End Sub

Function WorksheetExists(wsName As String, _
Optional wbName As String) As Boolean
If wbName = "" Then wbName = _
ActiveWorkbook.Name
On Error Resume Next
WorksheetExists = CBool(Len(Workbooks(wbName) _
.Worksheets(wsName).Name))
End Function

---

Vasant




"Max" wrote in message
...
Hi guys,

I'm looking for help with a sub* which can automate this process:

I've opened 3 books:
Summary_May2005.xls
11002404_abcdef.xls
11003702_abcdef.xls

In Summary.xls
there will be sheets named as: 11002404, 11003702, etc

These sheets are named with the project nos,
i.e. the first 8 digits of the books' filenames :
11002404_abcdef.xls
11003702_abcdef.xls

In each:
11002404_abcdef.xls
11003702_abcdef.xls

there will be a sheet named: Monthly Status Report

This sheet is the one which needs to be copied and pasted over as values
into
Summary_May2005.xls (into the corresponding sheets over there, overwriting
previous data.)

And if the sheet(s) to be copied/pasted over doesn't exist as yet in
Summary_May2005.xls (e.g.: new cases), the copy/paste will then be done in
a
new sheet(s) created which will then be named after the first 8 digits of
the file(s).

*sub to run from Summary_May2005.xls

Grateful for any insights. Thanks.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

A quick acknowledgement and BIG thanks, Vasant!
I'll certainly try your offering and post back further here. Cheers.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Sorry ... despite several tries running the sub, I kept hitting this error:

Run time error '9': Subscript out of range

Debug pointed at this line:

wb.Worksheets("Monthly Status Report").Cells.Copy _
wbSummary.Worksheets(Left(wb.Name, 8)).Range("A1")

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

In that case, most likely all the open workbooks (with the exception of the
destination workbook) do not contain a worksheet named "Monthly Status
Report". Or perhaps a hidden workbook such as Personal.xls is open (which of
course would not have a worksheet with this name).

--

Vasant




"Max" wrote in message
...
Sorry ... despite several tries running the sub, I kept hitting this
error:

Run time error '9': Subscript out of range

Debug pointed at this line:

wb.Worksheets("Monthly Status Report").Cells.Copy _
wbSummary.Worksheets(Left(wb.Name, 8)).Range("A1")

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

... Or perhaps a hidden workbook such as Personal.xls is open
(which of course would not have a worksheet with this name).

Yes, think this is the culprit. What can I do to proceed ?

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----