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 <at>yahoo<dot>com
----

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 <at>yahoo<dot>com
----

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" <[email protected]> wrote in message
news:[email protected]...
> 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 <at>yahoo<dot>com
> ----
>
>

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 <at>yahoo<dot>com
----

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 <at>yahoo<dot>com
----

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" <[email protected]> wrote in message
news:[email protected]...
> 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 <at>yahoo<dot>com
> ----
>
>

... 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 <at>yahoo<dot>com
----

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" <[email protected]> wrote in message
news:[email protected]...
> .. 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 <at>yahoo<dot>com
> ----
>
>

"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 <at>yahoo<dot>com
----

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" <[email protected]> wrote in message
news:[email protected]...
> "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 <at>yahoo<dot>com
> ----
>
>

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

--

Vasant

"Max" <[email protected]> wrote in message
news:[email protected]...
> "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 <at>yahoo<dot>com
> ----
>
>

"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 <at>yahoo<dot>com
----

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 <at>yahoo<dot>com
----

... 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 <at>yahoo<dot>com
----

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" <[email protected]> wrote in message
news:%23%[email protected]...
> .. 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 <at>yahoo<dot>com
> ----
>
>

"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 <at>yahoo<dot>com
----

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" <[email protected]> wrote in message
news:%[email protected]...
> "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 <at>yahoo<dot>com
> ----
>
>

> ... 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 <at>yahoo<dot>com
----

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

--

Vasant


"Max" <[email protected]> wrote in message
news:[email protected]...
> > ... 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 <at>yahoo<dot>com
> ----
>
>

"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 <at>yahoo<dot>com
----