Sub to copy a target sheet from slaves from and paste/overwrite into master
 

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
----

Sub to copy a target sheet from slaves from and paste/overwrite into master
 

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
----

Sub to copy a target sheet from slaves from and paste/overwrite into master
 

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
----

Sub to copy a target sheet from slaves from and paste/overwrite into master
 

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
----

Sub to copy a target sheet from slaves from and paste/overwrite into master
 

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
----

Sub to copy a target sheet from slaves from and paste/overwrite into master
 

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
----

Sub to copy a target sheet from slaves from and paste/overwrite into master
 

... 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
----

Sub to copy a target sheet from slaves from and paste/overwrite into master
 

Replace the line:

If wb.Name < wbSummary.Name Then

with:

If wb.Name < wbSummary.Name And wb.Name < "Personal.xls" Then

Of course, if other hidden workbooks are open, you will have to add
additional "And" conditions so that the macro will ignore them.

--

Vasant

"Max" wrote in message
...
.. 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
----

Sub to copy a target sheet from slaves from and paste/overwrite into master
 

"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote
Replace the line:
If wb.Name < wbSummary.Name Then
with:
If wb.Name < wbSummary.Name And wb.Name < "Personal.xls" Then

Of course, if other hidden workbooks are open, you will have to add
additional "And" conditions so that the macro will ignore them.

Thanks. Did the replacement, but the sub continued to freeze at the same
previous line. Personal.xls is the only hidden workbook. There was a new
sheet named: PERSONAL inserted into wbSummary before it froze.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Sub to copy a target sheet from slaves from and paste/overwrite into master
 

Clearly Personal.xls is causing the problem, but the modification I
suggested should have solved it. Can you post the complete code?

--

Vasant

"Max" wrote in message
...
"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote
Replace the line:
If wb.Name < wbSummary.Name Then
with:
If wb.Name < wbSummary.Name And wb.Name < "Personal.xls" Then

Of course, if other hidden workbooks are open, you will have to add
additional "And" conditions so that the macro will ignore them.

Thanks. Did the replacement, but the sub continued to freeze at the same
previous line. Personal.xls is the only hidden workbook. There was a new
sheet named: PERSONAL inserted into wbSummary before it froze.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Sub to copy a target sheet from slaves from and paste/overwrite into master
 

"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote
Clearly Personal.xls is causing the problem, but the modification I
suggested should have solved it. Can you post the complete code?

Here it is ..

Sub PasteNewInfo()
'Vasant .prog 12 Jun 2005
Dim wb As Workbook, wbSummary As Workbook
Set wbSummary = Workbooks("PMO_May05_Test1.xls")
For Each wb In Workbooks
If wb.Name < wbSummary.Name And wb.Name < "Personal.xls" Then
' 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

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

Sub to copy a target sheet from slaves from and paste/overwrite into master
 

Max, it works fine for me. Is it still creating a tab called "Personal"?
It's not doing that for me. Are you sure your Personal Macro Workbook
doesn't have a slightly different name from "Personal.xls"?

Also, are you sure you have a tab called "Monthly Summary Report" in each of
the source workbooks?

--

Vasant



"Max" wrote in message
...
"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote
Clearly Personal.xls is causing the problem, but the modification I
suggested should have solved it. Can you post the complete code?

Here it is ..

Sub PasteNewInfo()
'Vasant .prog 12 Jun 2005
Dim wb As Workbook, wbSummary As Workbook
Set wbSummary = Workbooks("PMO_May05_Test1.xls")
For Each wb In Workbooks
If wb.Name < wbSummary.Name And wb.Name < "Personal.xls" Then
' 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

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

Sub to copy a target sheet from slaves from and paste/overwrite into master
 

Thanks for your support and patience, Vasant ..

Is it still creating a tab called "Personal"?
It's not doing that for me.

Yes, unfortunately, it still creates this tab over here

Are you sure your Personal Macro Workbook
doesn't have a slightly different name from "Personal.xls"?

Confirmed, it's named: Personal.xls

But (ugh ... looking apologetically sheepish here ...) are .xla's hidden
workbooks which need to be excluded ? I do have quite a number of add-ins
loaded on Excel start-up here, which will be a real pain to unload/reload,
or to have these exclusions incorporated into the statement* you suggested
earlier. Is there a way to circumvent and have the sub ignore all the
add-in .xlas ?

* If wb.Name < wbSummary.Name And wb.Name < "Personal.xls" Then
Of course, if other hidden workbooks are open,
you will have to add additional "And" conditions
so that the macro will ignore them.

Also, are you sure you have a tab called
"Monthly Summary Report" in each of the source workbooks?

The target sheet is named: Monthly Status Report
as per orig. post, which I believe your sub captures correctly in the line:
wb.Worksheets("Monthly Status Report").Cells.Copy _

I've confirmed that this target sheet exists in each of the source workbooks
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Sub to copy a target sheet from slaves from and paste/overwrite into master
 

... I'm using xl97 (not sure whether this is of any relevance ..)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Sub to copy a target sheet from slaves from and paste/overwrite into master
 

The Excel version shouldn't matter. I don't know why this is happening, but
the only alternative I can think of is to close Personal.xls before you run
the macro. You can close it from the Immediate Window; it's easier.

Workbooks("Personal.xls").Close

--

Vasant





"Max" wrote in message
...
.. I'm using xl97 (not sure whether this is of any relevance ..)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Sub to copy a target sheet from slaves from and paste/overwrite into master
 

"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote
The Excel version shouldn't matter. I don't know why this is happening,
but
the only alternative I can think of is to close Personal.xls before you
run
the macro. You can close it from the Immediate Window; it's easier.
Workbooks("Personal.xls").Close

Yes, that did it! Brilliant! Ran smooth as silk, doing all the necessary
stuff from 18 source files in under 5 secs flat! Many thanks, Vasant!
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Sub to copy a target sheet from slaves from and paste/overwrite into master
 

Glad we were able to work it out, Max!

For future reference (I assume this will be a recurring project), are you
absolutely certain that your Personal Macro Workbook is called
"Personal.xls" and NOT "Personal Macro Workbook.xls"? I seem to recall that
it was called by the latter name in Excel 97. If that is the case, just
modify the macro line to refer to it by the correct name and then you won't
need to close it.

--

Vasant

"Max" wrote in message
...
"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote
The Excel version shouldn't matter. I don't know why this is happening,
but
the only alternative I can think of is to close Personal.xls before you
run
the macro. You can close it from the Immediate Window; it's easier.
Workbooks("Personal.xls").Close

Yes, that did it! Brilliant! Ran smooth as silk, doing all the necessary
stuff from 18 source files in under 5 secs flat! Many thanks, Vasant!
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Sub to copy a target sheet from slaves from and paste/overwrite into master
 

... are you absolutely certain that your Personal Macro Workbook
is called "Personal.xls" and NOT "Personal Macro Workbook.xls"?

Re-confirmed it's named: "Personal.xls", except for the *case* (which I
thought wasn't material earlier). In VBE project window, it appears as:
VBAProject (PERSONAL.XLS). And when I amended the reference in your sub to
match the case exactly, it ran w/o a hitch! Beautiful. Thanks again, Vasant!

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

Sub to copy a target sheet from slaves from and paste/overwrite into master
 

I didn't think the case was material either, but hey, as long as it works
.... :-)

--

Vasant


"Max" wrote in message
...
... are you absolutely certain that your Personal Macro Workbook
is called "Personal.xls" and NOT "Personal Macro Workbook.xls"?

Re-confirmed it's named: "Personal.xls", except for the *case* (which I
thought wasn't material earlier). In VBE project window, it appears as:
VBAProject (PERSONAL.XLS). And when I amended the reference in your sub
to
match the case exactly, it ran w/o a hitch! Beautiful. Thanks again,
Vasant!

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

Sub to copy a target sheet from slaves from and paste/overwrite into master
 

"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote
I didn't think the case was material either,
but hey, as long as it works ... :-)

And that's always the important bottom line <g Cheers.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----