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

[Max]

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.

 

[Max]

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.

 

[Vasant Nanavati]

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

 

[Max]

A quick acknowledgement and BIG thanks, Vasant!
I'll certainly try your offering and post back further here. Cheers.

 

[Max]

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

 

[Vasant Nanavati]

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

 

[Max]

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

 

[Vasant Nanavati]

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.

 

[Max]

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.

 

[Vasant Nanavati]

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

 

[Max]

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

 

[Vasant Nanavati]

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?

 

[Max]

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 ?

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

 

[Max]

... I'm using xl97 (not sure whether this is of any relevance ..)

 

[Vasant Nanavati]

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

 

[Max]

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!

 

[Vasant Nanavati]

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.

 

[Max]

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

 

[Vasant Nanavati]

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

 

[Max]

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.