Linking a Summary Workbook

I have 3 workbooks (and adding), with 20 worksheets each, with all workbooks
and worksheets laid out exactly the same, I want a summary workbook that
would add each worksheet from the 3 workbooks together so that the summary
workbook would have 20 worksheets exactly the same as the 3 originals.
i.e.C:\Documents and Settings\My Documents\[WB1.xls]Sheet1'!A1+C:\Documents
and Settings\My Documents\[WB2.xls]Sheet1'!A1+'C:\Documents and SettingsMy
Documents[WB3.xls]Sheet1'!A1.

I know that I can copy this across and down, but I do not want to do it that
way because it is very time consuming to add a new workbook and it won't be
long before I run out of character space in the formula.

I checked out Ron de Bruins site, but I did not find anything that adds the
sheet together.

I am looking for a way to add the workbooks or worksheets together and
easily add a new workbook. Any help or suggestions would be appreciated.

Hi Ronbo

How big is the range (one column or ??) that you want to sum from each sheet

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ronbo" <[email protected]> wrote in message news:[email protected]...
>I have 3 workbooks (and adding), with 20 worksheets each, with all workbooks
> and worksheets laid out exactly the same, I want a summary workbook that
> would add each worksheet from the 3 workbooks together so that the summary
> workbook would have 20 worksheets exactly the same as the 3 originals.
> i.e.C:\Documents and Settings\My Documents\[WB1.xls]Sheet1'!A1+C:\Documents
> and Settings\My Documents\[WB2.xls]Sheet1'!A1+'C:\Documents and SettingsMy
> Documents[WB3.xls]Sheet1'!A1.
>
> I know that I can copy this across and down, but I do not want to do it that
> way because it is very time consuming to add a new workbook and it won't be
> long before I run out of character space in the formula.
>
> I checked out Ron de Bruins site, but I did not find anything that adds the
> sheet together.
>
> I am looking for a way to add the workbooks or worksheets together and
> easily add a new workbook. Any help or suggestions would be appreciated.
>

Sub AABB()
Dim sPath As String
Dim v As Variant
Dim bk As Workbook
Dim bkSum As Workbook
Dim i As Long
Dim sh As Worksheet
Dim cell As Range
Dim rng As Range
sPath = "C:\Documents and Settings\MyDocuments\"
v = Array("WB1.xls", "WB2.xls", "WB3.xls")
Set bk = Workbooks.Open(sPath & v(LBound(v)))
bk.Worksheets.Copy
Set bkSum = ActiveWorkbook
bk.Close SaveChanges:=False
For i = LBound(v) + 1 To UBound(v)
Set bk = Workbooks.Open(sPath & v(LBound(v)))
For Each sh In bkSum.Worksheets
For Each cell In sh.UsedRange
If IsNumeric(cell.Value) Then
Set rng = bk.Worksheets(sh.Name).Range(cell.Address)
If IsNumeric(rng.Value) Then
cell.Value = cell.Value + rng.Value
End If
End If
Next
Next
Next
End Sub


--
Regards,
Tom Ogilvy




"Ronbo" <[email protected]> wrote in message
news:[email protected]...
> I have 3 workbooks (and adding), with 20 worksheets each, with all
workbooks
> and worksheets laid out exactly the same, I want a summary workbook that
> would add each worksheet from the 3 workbooks together so that the summary
> workbook would have 20 worksheets exactly the same as the 3 originals.
> i.e.C:\Documents and Settings\My
Documents\[WB1.xls]Sheet1'!A1+C:\Documents
> and Settings\My Documents\[WB2.xls]Sheet1'!A1+'C:\Documents and SettingsMy
> Documents[WB3.xls]Sheet1'!A1.
>
> I know that I can copy this across and down, but I do not want to do it
that
> way because it is very time consuming to add a new workbook and it won't
be
> long before I run out of character space in the formula.
>
> I checked out Ron de Bruins site, but I did not find anything that adds
the
> sheet together.
>
> I am looking for a way to add the workbooks or worksheets together and
> easily add a new workbook. Any help or suggestions would be appreciated.
>

They vary, but usually around 14 columns. The largest is 14 columns by 2500
rows.




"Ron de Bruin" wrote:

> Hi Ronbo
>
> How big is the range (one column or ??) that you want to sum from each sheet
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "Ronbo" <[email protected]> wrote in message news:[email protected]...
> >I have 3 workbooks (and adding), with 20 worksheets each, with all workbooks
> > and worksheets laid out exactly the same, I want a summary workbook that
> > would add each worksheet from the 3 workbooks together so that the summary
> > workbook would have 20 worksheets exactly the same as the 3 originals.
> > i.e.C:\Documents and Settings\My Documents\[WB1.xls]Sheet1'!A1+C:\Documents
> > and Settings\My Documents\[WB2.xls]Sheet1'!A1+'C:\Documents and SettingsMy
> > Documents[WB3.xls]Sheet1'!A1.
> >
> > I know that I can copy this across and down, but I do not want to do it that
> > way because it is very time consuming to add a new workbook and it won't be
> > long before I run out of character space in the formula.
> >
> > I checked out Ron de Bruins site, but I did not find anything that adds the
> > sheet together.
> >
> > I am looking for a way to add the workbooks or worksheets together and
> > easily add a new workbook. Any help or suggestions would be appreciated.
> >
>
>
>

Try tom's example first and post back if you want something else.

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ronbo" <[email protected]> wrote in message news:[email protected]...
> They vary, but usually around 14 columns. The largest is 14 columns by 2500
> rows.
>
>
>
>
> "Ron de Bruin" wrote:
>
>> Hi Ronbo
>>
>> How big is the range (one column or ??) that you want to sum from each sheet
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>> "Ronbo" <[email protected]> wrote in message news:[email protected]...
>> >I have 3 workbooks (and adding), with 20 worksheets each, with all workbooks
>> > and worksheets laid out exactly the same, I want a summary workbook that
>> > would add each worksheet from the 3 workbooks together so that the summary
>> > workbook would have 20 worksheets exactly the same as the 3 originals.
>> > i.e.C:\Documents and Settings\My Documents\[WB1.xls]Sheet1'!A1+C:\Documents
>> > and Settings\My Documents\[WB2.xls]Sheet1'!A1+'C:\Documents and SettingsMy
>> > Documents[WB3.xls]Sheet1'!A1.
>> >
>> > I know that I can copy this across and down, but I do not want to do it that
>> > way because it is very time consuming to add a new workbook and it won't be
>> > long before I run out of character space in the formula.
>> >
>> > I checked out Ron de Bruins site, but I did not find anything that adds the
>> > sheet together.
>> >
>> > I am looking for a way to add the workbooks or worksheets together and
>> > easily add a new workbook. Any help or suggestions would be appreciated.
>> >
>>
>>
>>

Tom:

Thanks alot. Thats cool. It works perfect.

What would I do if I only wanted to add only Worksheet3 and worksheet7?

"Tom Ogilvy" wrote:

> Sub AABB()
> Dim sPath As String
> Dim v As Variant
> Dim bk As Workbook
> Dim bkSum As Workbook
> Dim i As Long
> Dim sh As Worksheet
> Dim cell As Range
> Dim rng As Range
> sPath = "C:\Documents and Settings\MyDocuments\"
> v = Array("WB1.xls", "WB2.xls", "WB3.xls")
> Set bk = Workbooks.Open(sPath & v(LBound(v)))
> bk.Worksheets.Copy
> Set bkSum = ActiveWorkbook
> bk.Close SaveChanges:=False
> For i = LBound(v) + 1 To UBound(v)
> Set bk = Workbooks.Open(sPath & v(LBound(v)))
> For Each sh In bkSum.Worksheets
> For Each cell In sh.UsedRange
> If IsNumeric(cell.Value) Then
> Set rng = bk.Worksheets(sh.Name).Range(cell.Address)
> If IsNumeric(rng.Value) Then
> cell.Value = cell.Value + rng.Value
> End If
> End If
> Next
> Next
> Next
> End Sub
>
>
> --
> Regards,
> Tom Ogilvy
>
>
>
>
> "Ronbo" <[email protected]> wrote in message
> news:[email protected]...
> > I have 3 workbooks (and adding), with 20 worksheets each, with all
> workbooks
> > and worksheets laid out exactly the same, I want a summary workbook that
> > would add each worksheet from the 3 workbooks together so that the summary
> > workbook would have 20 worksheets exactly the same as the 3 originals.
> > i.e.C:\Documents and Settings\My
> Documents\[WB1.xls]Sheet1'!A1+C:\Documents
> > and Settings\My Documents\[WB2.xls]Sheet1'!A1+'C:\Documents and SettingsMy
> > Documents[WB3.xls]Sheet1'!A1.
> >
> > I know that I can copy this across and down, but I do not want to do it
> that
> > way because it is very time consuming to add a new workbook and it won't
> be
> > long before I run out of character space in the formula.
> >
> > I checked out Ron de Bruins site, but I did not find anything that adds
> the
> > sheet together.
> >
> > I am looking for a way to add the workbooks or worksheets together and
> > easily add a new workbook. Any help or suggestions would be appreciated.
> >
>
>
>

> Sub AABB()
> Dim sPath As String
> Dim v As Variant
> Dim bk As Workbook
> Dim bkSum As Workbook
> Dim i As Long
> Dim sh As Worksheet
> Dim cell As Range
> Dim rng As Range
Dim sh as worksheet
> sPath = "C:\Documents and Settings\MyDocuments\"
> v = Array("WB1.xls", "WB2.xls", "WB3.xls")
> Set bk = Workbooks.Open(sPath & v(LBound(v)))
> bk.Worksheets.Copy
> Set bkSum = ActiveWorkbook
for each sh in bkSum.worksheets
if lcase(sh.name) <> "sheet3" and lcase(sh.name) <> "sheet7" then
application.displayalerts = False
sh.Delete
application.Displayalerts = True
end if
Next
> bk.Close SaveChanges:=False
> For i = LBound(v) + 1 To UBound(v)
> Set bk = Workbooks.Open(sPath & v(LBound(v)))
> For Each sh In bkSum.Worksheets
> For Each cell In sh.UsedRange
> If IsNumeric(cell.Value) Then
> Set rng = bk.Worksheets(sh.Name).Range(cell.Address)
> If IsNumeric(rng.Value) Then
> cell.Value = cell.Value + rng.Value
> End If
> End If
> Next
> Next
> Next
> End Sub


Adjust names to match your actual situation.
--
Regards,
Tom Ogilvy


"Ronbo" <[email protected]> wrote in message
news:[email protected]...
> Tom:
>
> Thanks alot. Thats cool. It works perfect.
>
> What would I do if I only wanted to add only Worksheet3 and worksheet7?
>
> "Tom Ogilvy" wrote:
>
> > Sub AABB()
> > Dim sPath As String
> > Dim v As Variant
> > Dim bk As Workbook
> > Dim bkSum As Workbook
> > Dim i As Long
> > Dim sh As Worksheet
> > Dim cell As Range
> > Dim rng As Range
> > sPath = "C:\Documents and Settings\MyDocuments\"
> > v = Array("WB1.xls", "WB2.xls", "WB3.xls")
> > Set bk = Workbooks.Open(sPath & v(LBound(v)))
> > bk.Worksheets.Copy
> > Set bkSum = ActiveWorkbook
> > bk.Close SaveChanges:=False
> > For i = LBound(v) + 1 To UBound(v)
> > Set bk = Workbooks.Open(sPath & v(LBound(v)))
> > For Each sh In bkSum.Worksheets
> > For Each cell In sh.UsedRange
> > If IsNumeric(cell.Value) Then
> > Set rng = bk.Worksheets(sh.Name).Range(cell.Address)
> > If IsNumeric(rng.Value) Then
> > cell.Value = cell.Value + rng.Value
> > End If
> > End If
> > Next
> > Next
> > Next
> > End Sub
> >
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> >
> > "Ronbo" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have 3 workbooks (and adding), with 20 worksheets each, with all
> > workbooks
> > > and worksheets laid out exactly the same, I want a summary workbook
that
> > > would add each worksheet from the 3 workbooks together so that the
summary
> > > workbook would have 20 worksheets exactly the same as the 3 originals.
> > > i.e.C:\Documents and Settings\My
> > Documents\[WB1.xls]Sheet1'!A1+C:\Documents
> > > and Settings\My Documents\[WB2.xls]Sheet1'!A1+'C:\Documents and
SettingsMy
> > > Documents[WB3.xls]Sheet1'!A1.
> > >
> > > I know that I can copy this across and down, but I do not want to do
it
> > that
> > > way because it is very time consuming to add a new workbook and it
won't
> > be
> > > long before I run out of character space in the formula.
> > >
> > > I checked out Ron de Bruins site, but I did not find anything that
adds
> > the
> > > sheet together.
> > >
> > > I am looking for a way to add the workbooks or worksheets together and
> > > easily add a new workbook. Any help or suggestions would be
appreciated.
> > >
> >
> >
> >

Tom:

Thanks, it works perfect (in my test situation). Implementing it into my
real situation I realized that the files are in differant directories. How
do I compensate for that? Also, is there a way to put this routine in to a
Summary Workbook, rather than having it creating a new book? I would like to
use the same workbook each month with the layout and macros.

Sorry for all of the questions, but this level of programming is way beyond
my skills.
I am soon going to quit for the weekend. How do I get back to this topic on
Monday?
Start a new question or come back here?

Again, Thanks a lot (as always) for your help.



"Tom Ogilvy" wrote:

> > Sub AABB()
> > Dim sPath As String
> > Dim v As Variant
> > Dim bk As Workbook
> > Dim bkSum As Workbook
> > Dim i As Long
> > Dim sh As Worksheet
> > Dim cell As Range
> > Dim rng As Range
> Dim sh as worksheet
> > sPath = "C:\Documents and Settings\MyDocuments\"
> > v = Array("WB1.xls", "WB2.xls", "WB3.xls")
> > Set bk = Workbooks.Open(sPath & v(LBound(v)))
> > bk.Worksheets.Copy
> > Set bkSum = ActiveWorkbook
> for each sh in bkSum.worksheets
> if lcase(sh.name) <> "sheet3" and lcase(sh.name) <> "sheet7" then
> application.displayalerts = False
> sh.Delete
> application.Displayalerts = True
> end if
> Next
> > bk.Close SaveChanges:=False
> > For i = LBound(v) + 1 To UBound(v)
> > Set bk = Workbooks.Open(sPath & v(LBound(v)))
> > For Each sh In bkSum.Worksheets
> > For Each cell In sh.UsedRange
> > If IsNumeric(cell.Value) Then
> > Set rng = bk.Worksheets(sh.Name).Range(cell.Address)
> > If IsNumeric(rng.Value) Then
> > cell.Value = cell.Value + rng.Value
> > End If
> > End If
> > Next
> > Next
> > Next
> > End Sub
>
>
> Adjust names to match your actual situation.
> --
> Regards,
> Tom Ogilvy
>
>
> "Ronbo" <[email protected]> wrote in message
> news:[email protected]...
> > Tom:
> >
> > Thanks alot. Thats cool. It works perfect.
> >
> > What would I do if I only wanted to add only Worksheet3 and worksheet7?
> >
> > "Tom Ogilvy" wrote:
> >
> > > Sub AABB()
> > > Dim sPath As String
> > > Dim v As Variant
> > > Dim bk As Workbook
> > > Dim bkSum As Workbook
> > > Dim i As Long
> > > Dim sh As Worksheet
> > > Dim cell As Range
> > > Dim rng As Range
> > > sPath = "C:\Documents and Settings\MyDocuments\"
> > > v = Array("WB1.xls", "WB2.xls", "WB3.xls")
> > > Set bk = Workbooks.Open(sPath & v(LBound(v)))
> > > bk.Worksheets.Copy
> > > Set bkSum = ActiveWorkbook
> > > bk.Close SaveChanges:=False
> > > For i = LBound(v) + 1 To UBound(v)
> > > Set bk = Workbooks.Open(sPath & v(LBound(v)))
> > > For Each sh In bkSum.Worksheets
> > > For Each cell In sh.UsedRange
> > > If IsNumeric(cell.Value) Then
> > > Set rng = bk.Worksheets(sh.Name).Range(cell.Address)
> > > If IsNumeric(rng.Value) Then
> > > cell.Value = cell.Value + rng.Value
> > > End If
> > > End If
> > > Next
> > > Next
> > > Next
> > > End Sub
> > >
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > >
> > >
> > > "Ronbo" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I have 3 workbooks (and adding), with 20 worksheets each, with all
> > > workbooks
> > > > and worksheets laid out exactly the same, I want a summary workbook
> that
> > > > would add each worksheet from the 3 workbooks together so that the
> summary
> > > > workbook would have 20 worksheets exactly the same as the 3 originals.
> > > > i.e.C:\Documents and Settings\My
> > > Documents\[WB1.xls]Sheet1'!A1+C:\Documents
> > > > and Settings\My Documents\[WB2.xls]Sheet1'!A1+'C:\Documents and
> SettingsMy
> > > > Documents[WB3.xls]Sheet1'!A1.
> > > >
> > > > I know that I can copy this across and down, but I do not want to do
> it
> > > that
> > > > way because it is very time consuming to add a new workbook and it
> won't
> > > be
> > > > long before I run out of character space in the formula.
> > > >
> > > > I checked out Ron de Bruins site, but I did not find anything that
> adds
> > > the
> > > > sheet together.
> > > >
> > > > I am looking for a way to add the workbooks or worksheets together and
> > > > easily add a new workbook. Any help or suggestions would be
> appreciated.
> > > >
> > >
> > >
> > >
>
>
>

Tom:

I tried it on the real thing and most cells came up with #REF!. Thses are
cells that are formulas. They make up about 98% of all cells. ?


"Ronbo" wrote:

> Tom:
>
> Thanks, it works perfect (in my test situation). Implementing it into my
> real situation I realized that the files are in differant directories. How
> do I compensate for that? Also, is there a way to put this routine in to a
> Summary Workbook, rather than having it creating a new book? I would like to
> use the same workbook each month with the layout and macros.
>
> Sorry for all of the questions, but this level of programming is way beyond
> my skills.
> I am soon going to quit for the weekend. How do I get back to this topic on
> Monday?
> Start a new question or come back here?
>
> Again, Thanks a lot (as always) for your help.
>
>
>
> "Tom Ogilvy" wrote:
>
> > > Sub AABB()
> > > Dim sPath As String
> > > Dim v As Variant
> > > Dim bk As Workbook
> > > Dim bkSum As Workbook
> > > Dim i As Long
> > > Dim sh As Worksheet
> > > Dim cell As Range
> > > Dim rng As Range
> > Dim sh as worksheet
> > > sPath = "C:\Documents and Settings\MyDocuments\"
> > > v = Array("WB1.xls", "WB2.xls", "WB3.xls")
> > > Set bk = Workbooks.Open(sPath & v(LBound(v)))
> > > bk.Worksheets.Copy
> > > Set bkSum = ActiveWorkbook
> > for each sh in bkSum.worksheets
> > if lcase(sh.name) <> "sheet3" and lcase(sh.name) <> "sheet7" then
> > application.displayalerts = False
> > sh.Delete
> > application.Displayalerts = True
> > end if
> > Next
> > > bk.Close SaveChanges:=False
> > > For i = LBound(v) + 1 To UBound(v)
> > > Set bk = Workbooks.Open(sPath & v(LBound(v)))
> > > For Each sh In bkSum.Worksheets
> > > For Each cell In sh.UsedRange
> > > If IsNumeric(cell.Value) Then
> > > Set rng = bk.Worksheets(sh.Name).Range(cell.Address)
> > > If IsNumeric(rng.Value) Then
> > > cell.Value = cell.Value + rng.Value
> > > End If
> > > End If
> > > Next
> > > Next
> > > Next
> > > End Sub
> >
> >
> > Adjust names to match your actual situation.
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Ronbo" <[email protected]> wrote in message
> > news:[email protected]...
> > > Tom:
> > >
> > > Thanks alot. Thats cool. It works perfect.
> > >
> > > What would I do if I only wanted to add only Worksheet3 and worksheet7?
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > Sub AABB()
> > > > Dim sPath As String
> > > > Dim v As Variant
> > > > Dim bk As Workbook
> > > > Dim bkSum As Workbook
> > > > Dim i As Long
> > > > Dim sh As Worksheet
> > > > Dim cell As Range
> > > > Dim rng As Range
> > > > sPath = "C:\Documents and Settings\MyDocuments\"
> > > > v = Array("WB1.xls", "WB2.xls", "WB3.xls")
> > > > Set bk = Workbooks.Open(sPath & v(LBound(v)))
> > > > bk.Worksheets.Copy
> > > > Set bkSum = ActiveWorkbook
> > > > bk.Close SaveChanges:=False
> > > > For i = LBound(v) + 1 To UBound(v)
> > > > Set bk = Workbooks.Open(sPath & v(LBound(v)))
> > > > For Each sh In bkSum.Worksheets
> > > > For Each cell In sh.UsedRange
> > > > If IsNumeric(cell.Value) Then
> > > > Set rng = bk.Worksheets(sh.Name).Range(cell.Address)
> > > > If IsNumeric(rng.Value) Then
> > > > cell.Value = cell.Value + rng.Value
> > > > End If
> > > > End If
> > > > Next
> > > > Next
> > > > Next
> > > > End Sub
> > > >
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > >
> > > >
> > > > "Ronbo" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > I have 3 workbooks (and adding), with 20 worksheets each, with all
> > > > workbooks
> > > > > and worksheets laid out exactly the same, I want a summary workbook
> > that
> > > > > would add each worksheet from the 3 workbooks together so that the
> > summary
> > > > > workbook would have 20 worksheets exactly the same as the 3 originals.
> > > > > i.e.C:\Documents and Settings\My
> > > > Documents\[WB1.xls]Sheet1'!A1+C:\Documents
> > > > > and Settings\My Documents\[WB2.xls]Sheet1'!A1+'C:\Documents and
> > SettingsMy
> > > > > Documents[WB3.xls]Sheet1'!A1.
> > > > >
> > > > > I know that I can copy this across and down, but I do not want to do
> > it
> > > > that
> > > > > way because it is very time consuming to add a new workbook and it
> > won't
> > > > be
> > > > > long before I run out of character space in the formula.
> > > > >
> > > > > I checked out Ron de Bruins site, but I did not find anything that
> > adds
> > > > the
> > > > > sheet together.
> > > > >
> > > > > I am looking for a way to add the workbooks or worksheets together and
> > > > > easily add a new workbook. Any help or suggestions would be
> > appreciated.
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Sub AABB()
Dim sPath As String
Dim v As Variant
Dim bk As Workbook
Dim bkSum As Workbook
Dim i As Long
Dim sh As Worksheet
Dim cell As Range
Dim rng As Range
Dim sh as worksheet
sPath = "C:\Documents and Settings\MyDocuments\"
v = Array("WB1.xls", "WB2.xls", "WB3.xls")
Set bk = Workbooks.Open(sPath & v(LBound(v)))
bk.Worksheets.Copy
Set bkSum = ActiveWorkbook
for each sh in bkSum.worksheets
if lcase(sh.name) <> "sheet3" and lcase(sh.name) <> "sheet7" then
application.displayalerts = False
sh.Delete
application.Displayalerts = True
else
sh.UsedRange.Formula = sh.UsedRange.Value
end if
Next
bk.Close SaveChanges:=False
For i = LBound(v) + 1 To UBound(v)
Set bk = Workbooks.Open(sPath & v(LBound(v)))
For Each sh In bkSum.Worksheets
For Each cell In sh.UsedRange
If IsNumeric(cell.Value) Then
Set rng = bk.Worksheets(sh.Name).Range(cell.Address)
If IsNumeric(rng.Value) Then
cell.Value = cell.Value + rng.Value
End If
End If
Next
Next
Next
End Sub


If the formulas refer to other sheets, then the loop might have to be
changed.

--
Regards,
Tom Ogilvy

"Ronbo" <[email protected]> wrote in message
news:[email protected]...
> Tom:
>
> I tried it on the real thing and most cells came up with #REF!. Thses are
> cells that are formulas. They make up about 98% of all cells. ?
>
>
> "Ronbo" wrote:
>
> > Tom:
> >
> > Thanks, it works perfect (in my test situation). Implementing it into
my
> > real situation I realized that the files are in differant directories.
How
> > do I compensate for that? Also, is there a way to put this routine in
to a
> > Summary Workbook, rather than having it creating a new book? I would
like to
> > use the same workbook each month with the layout and macros.
> >
> > Sorry for all of the questions, but this level of programming is way
beyond
> > my skills.
> > I am soon going to quit for the weekend. How do I get back to this topic
on
> > Monday?
> > Start a new question or come back here?
> >
> > Again, Thanks a lot (as always) for your help.
> >
> >
> >
> > "Tom Ogilvy" wrote:
> >
> > > > Sub AABB()
> > > > Dim sPath As String
> > > > Dim v As Variant
> > > > Dim bk As Workbook
> > > > Dim bkSum As Workbook
> > > > Dim i As Long
> > > > Dim sh As Worksheet
> > > > Dim cell As Range
> > > > Dim rng As Range
> > > Dim sh as worksheet
> > > > sPath = "C:\Documents and Settings\MyDocuments\"
> > > > v = Array("WB1.xls", "WB2.xls", "WB3.xls")
> > > > Set bk = Workbooks.Open(sPath & v(LBound(v)))
> > > > bk.Worksheets.Copy
> > > > Set bkSum = ActiveWorkbook
> > > for each sh in bkSum.worksheets
> > > if lcase(sh.name) <> "sheet3" and lcase(sh.name) <> "sheet7" then
> > > application.displayalerts = False
> > > sh.Delete
> > > application.Displayalerts = True
> > > end if
> > > Next
> > > > bk.Close SaveChanges:=False
> > > > For i = LBound(v) + 1 To UBound(v)
> > > > Set bk = Workbooks.Open(sPath & v(LBound(v)))
> > > > For Each sh In bkSum.Worksheets
> > > > For Each cell In sh.UsedRange
> > > > If IsNumeric(cell.Value) Then
> > > > Set rng = bk.Worksheets(sh.Name).Range(cell.Address)
> > > > If IsNumeric(rng.Value) Then
> > > > cell.Value = cell.Value + rng.Value
> > > > End If
> > > > End If
> > > > Next
> > > > Next
> > > > Next
> > > > End Sub
> > >
> > >
> > > Adjust names to match your actual situation.
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "Ronbo" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Tom:
> > > >
> > > > Thanks alot. Thats cool. It works perfect.
> > > >
> > > > What would I do if I only wanted to add only Worksheet3 and
worksheet7?
> > > >
> > > > "Tom Ogilvy" wrote:
> > > >
> > > > > Sub AABB()
> > > > > Dim sPath As String
> > > > > Dim v As Variant
> > > > > Dim bk As Workbook
> > > > > Dim bkSum As Workbook
> > > > > Dim i As Long
> > > > > Dim sh As Worksheet
> > > > > Dim cell As Range
> > > > > Dim rng As Range
> > > > > sPath = "C:\Documents and Settings\MyDocuments\"
> > > > > v = Array("WB1.xls", "WB2.xls", "WB3.xls")
> > > > > Set bk = Workbooks.Open(sPath & v(LBound(v)))
> > > > > bk.Worksheets.Copy
> > > > > Set bkSum = ActiveWorkbook
> > > > > bk.Close SaveChanges:=False
> > > > > For i = LBound(v) + 1 To UBound(v)
> > > > > Set bk = Workbooks.Open(sPath & v(LBound(v)))
> > > > > For Each sh In bkSum.Worksheets
> > > > > For Each cell In sh.UsedRange
> > > > > If IsNumeric(cell.Value) Then
> > > > > Set rng = bk.Worksheets(sh.Name).Range(cell.Address)
> > > > > If IsNumeric(rng.Value) Then
> > > > > cell.Value = cell.Value + rng.Value
> > > > > End If
> > > > > End If
> > > > > Next
> > > > > Next
> > > > > Next
> > > > > End Sub
> > > > >
> > > > >
> > > > > --
> > > > > Regards,
> > > > > Tom Ogilvy
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "Ronbo" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > I have 3 workbooks (and adding), with 20 worksheets each, with
all
> > > > > workbooks
> > > > > > and worksheets laid out exactly the same, I want a summary
workbook
> > > that
> > > > > > would add each worksheet from the 3 workbooks together so that
the
> > > summary
> > > > > > workbook would have 20 worksheets exactly the same as the 3
originals.
> > > > > > i.e.C:\Documents and Settings\My
> > > > > Documents\[WB1.xls]Sheet1'!A1+C:\Documents
> > > > > > and Settings\My Documents\[WB2.xls]Sheet1'!A1+'C:\Documents and
> > > SettingsMy
> > > > > > Documents[WB3.xls]Sheet1'!A1.
> > > > > >
> > > > > > I know that I can copy this across and down, but I do not want
to do
> > > it
> > > > > that
> > > > > > way because it is very time consuming to add a new workbook and
it
> > > won't
> > > > > be
> > > > > > long before I run out of character space in the formula.
> > > > > >
> > > > > > I checked out Ron de Bruins site, but I did not find anything
that
> > > adds
> > > > > the
> > > > > > sheet together.
> > > > > >
> > > > > > I am looking for a way to add the workbooks or worksheets
together and
> > > > > > easily add a new workbook. Any help or suggestions would be
> > > appreciated.
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >

Tom -

When I and the two lines of new code;
else
sh.UsedRange.Formula = sh.UsedRange.Value
I get;
Compile error
Syntax errror

What am I doing wrong??

Thanks

"Tom Ogilvy" wrote:

> Sub AABB()
> Dim sPath As String
> Dim v As Variant
> Dim bk As Workbook
> Dim bkSum As Workbook
> Dim i As Long
> Dim sh As Worksheet
> Dim cell As Range
> Dim rng As Range
> Dim sh as worksheet
> sPath = "C:\Documents and Settings\MyDocuments\"
> v = Array("WB1.xls", "WB2.xls", "WB3.xls")
> Set bk = Workbooks.Open(sPath & v(LBound(v)))
> bk.Worksheets.Copy
> Set bkSum = ActiveWorkbook
> for each sh in bkSum.worksheets
> if lcase(sh.name) <> "sheet3" and lcase(sh.name) <> "sheet7" then
> application.displayalerts = False
> sh.Delete
> application.Displayalerts = True
> else
> sh.UsedRange.Formula = sh.UsedRange.Value
> end if
> Next
> bk.Close SaveChanges:=False
> For i = LBound(v) + 1 To UBound(v)
> Set bk = Workbooks.Open(sPath & v(LBound(v)))
> For Each sh In bkSum.Worksheets
> For Each cell In sh.UsedRange
> If IsNumeric(cell.Value) Then
> Set rng = bk.Worksheets(sh.Name).Range(cell.Address)
> If IsNumeric(rng.Value) Then
> cell.Value = cell.Value + rng.Value
> End If
> End If
> Next
> Next
> Next
> End Sub
>
>
> If the formulas refer to other sheets, then the loop might have to be
> changed.
>
> --
> Regards,
> Tom Ogilvy
>
> "Ronbo" <[email protected]> wrote in message
> news:[email protected]...
> > Tom:
> >
> > I tried it on the real thing and most cells came up with #REF!. Thses are
> > cells that are formulas. They make up about 98% of all cells. ?
> >
> >
> > "Ronbo" wrote:
> >
> > > Tom:
> > >
> > > Thanks, it works perfect (in my test situation). Implementing it into
> my
> > > real situation I realized that the files are in differant directories.
> How
> > > do I compensate for that? Also, is there a way to put this routine in
> to a
> > > Summary Workbook, rather than having it creating a new book? I would
> like to
> > > use the same workbook each month with the layout and macros.
> > >
> > > Sorry for all of the questions, but this level of programming is way
> beyond
> > > my skills.
> > > I am soon going to quit for the weekend. How do I get back to this topic
> on
> > > Monday?
> > > Start a new question or come back here?
> > >
> > > Again, Thanks a lot (as always) for your help.
> > >
> > >
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > > Sub AABB()
> > > > > Dim sPath As String
> > > > > Dim v As Variant
> > > > > Dim bk As Workbook
> > > > > Dim bkSum As Workbook
> > > > > Dim i As Long
> > > > > Dim sh As Worksheet
> > > > > Dim cell As Range
> > > > > Dim rng As Range
> > > > Dim sh as worksheet
> > > > > sPath = "C:\Documents and Settings\MyDocuments\"
> > > > > v = Array("WB1.xls", "WB2.xls", "WB3.xls")
> > > > > Set bk = Workbooks.Open(sPath & v(LBound(v)))
> > > > > bk.Worksheets.Copy
> > > > > Set bkSum = ActiveWorkbook
> > > > for each sh in bkSum.worksheets
> > > > if lcase(sh.name) <> "sheet3" and lcase(sh.name) <> "sheet7" then
> > > > application.displayalerts = False
> > > > sh.Delete
> > > > application.Displayalerts = True
> > > > end if
> > > > Next
> > > > > bk.Close SaveChanges:=False
> > > > > For i = LBound(v) + 1 To UBound(v)
> > > > > Set bk = Workbooks.Open(sPath & v(LBound(v)))
> > > > > For Each sh In bkSum.Worksheets
> > > > > For Each cell In sh.UsedRange
> > > > > If IsNumeric(cell.Value) Then
> > > > > Set rng = bk.Worksheets(sh.Name).Range(cell.Address)
> > > > > If IsNumeric(rng.Value) Then
> > > > > cell.Value = cell.Value + rng.Value
> > > > > End If
> > > > > End If
> > > > > Next
> > > > > Next
> > > > > Next
> > > > > End Sub
> > > >
> > > >
> > > > Adjust names to match your actual situation.
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > > "Ronbo" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Tom:
> > > > >
> > > > > Thanks alot. Thats cool. It works perfect.
> > > > >
> > > > > What would I do if I only wanted to add only Worksheet3 and
> worksheet7?
> > > > >
> > > > > "Tom Ogilvy" wrote:
> > > > >
> > > > > > Sub AABB()
> > > > > > Dim sPath As String
> > > > > > Dim v As Variant
> > > > > > Dim bk As Workbook
> > > > > > Dim bkSum As Workbook
> > > > > > Dim i As Long
> > > > > > Dim sh As Worksheet
> > > > > > Dim cell As Range
> > > > > > Dim rng As Range
> > > > > > sPath = "C:\Documents and Settings\MyDocuments\"
> > > > > > v = Array("WB1.xls", "WB2.xls", "WB3.xls")
> > > > > > Set bk = Workbooks.Open(sPath & v(LBound(v)))
> > > > > > bk.Worksheets.Copy
> > > > > > Set bkSum = ActiveWorkbook
> > > > > > bk.Close SaveChanges:=False
> > > > > > For i = LBound(v) + 1 To UBound(v)
> > > > > > Set bk = Workbooks.Open(sPath & v(LBound(v)))
> > > > > > For Each sh In bkSum.Worksheets
> > > > > > For Each cell In sh.UsedRange
> > > > > > If IsNumeric(cell.Value) Then
> > > > > > Set rng = bk.Worksheets(sh.Name).Range(cell.Address)
> > > > > > If IsNumeric(rng.Value) Then
> > > > > > cell.Value = cell.Value + rng.Value
> > > > > > End If
> > > > > > End If
> > > > > > Next
> > > > > > Next
> > > > > > Next
> > > > > > End Sub
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Regards,
> > > > > > Tom Ogilvy
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Ronbo" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > I have 3 workbooks (and adding), with 20 worksheets each, with
> all
> > > > > > workbooks
> > > > > > > and worksheets laid out exactly the same, I want a summary
> workbook
> > > > that
> > > > > > > would add each worksheet from the 3 workbooks together so that
> the
> > > > summary
> > > > > > > workbook would have 20 worksheets exactly the same as the 3
> originals.
> > > > > > > i.e.C:\Documents and Settings\My
> > > > > > Documents\[WB1.xls]Sheet1'!A1+C:\Documents
> > > > > > > and Settings\My Documents\[WB2.xls]Sheet1'!A1+'C:\Documents and
> > > > SettingsMy
> > > > > > > Documents[WB3.xls]Sheet1'!A1.
> > > > > > >
> > > > > > > I know that I can copy this across and down, but I do not want
> to do
> > > > it
> > > > > > that
> > > > > > > way because it is very time consuming to add a new workbook and
> it
> > > > won't
> > > > > > be
> > > > > > > long before I run out of character space in the formula.
> > > > > > >
> > > > > > > I checked out Ron de Bruins site, but I did not find anything
> that
> > > > adds
> > > > > > the
> > > > > > > sheet together.
> > > > > > >
> > > > > > > I am looking for a way to add the workbooks or worksheets
> together and
> > > > > > > easily add a new workbook. Any help or suggestions would be
> > > > appreciated.
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
>
>
>

Tom -

I got it to work, must have typed in something wrong. So now it works, but
the
formulas do refer to other sheets the routine only adds the value from the
last workbook.

How do I change the loop to get it to add all three??

Thanks for any help.

"Ronbo" wrote:

> Tom -
>
> When I and the two lines of new code;
> else
> sh.UsedRange.Formula = sh.UsedRange.Value
> I get;
> Compile error
> Syntax errror
>
> What am I doing wrong??
>
> Thanks
>
> "Tom Ogilvy" wrote:
>
> > Sub AABB()
> > Dim sPath As String
> > Dim v As Variant
> > Dim bk As Workbook
> > Dim bkSum As Workbook
> > Dim i As Long
> > Dim sh As Worksheet
> > Dim cell As Range
> > Dim rng As Range
> > Dim sh as worksheet
> > sPath = "C:\Documents and Settings\MyDocuments\"
> > v = Array("WB1.xls", "WB2.xls", "WB3.xls")
> > Set bk = Workbooks.Open(sPath & v(LBound(v)))
> > bk.Worksheets.Copy
> > Set bkSum = ActiveWorkbook
> > for each sh in bkSum.worksheets
> > if lcase(sh.name) <> "sheet3" and lcase(sh.name) <> "sheet7" then
> > application.displayalerts = False
> > sh.Delete
> > application.Displayalerts = True
> > else
> > sh.UsedRange.Formula = sh.UsedRange.Value
> > end if
> > Next
> > bk.Close SaveChanges:=False
> > For i = LBound(v) + 1 To UBound(v)
> > Set bk = Workbooks.Open(sPath & v(LBound(v)))
> > For Each sh In bkSum.Worksheets
> > For Each cell In sh.UsedRange
> > If IsNumeric(cell.Value) Then
> > Set rng = bk.Worksheets(sh.Name).Range(cell.Address)
> > If IsNumeric(rng.Value) Then
> > cell.Value = cell.Value + rng.Value
> > End If
> > End If
> > Next
> > Next
> > Next
> > End Sub
> >
> >
> > If the formulas refer to other sheets, then the loop might have to be
> > changed.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Ronbo" <[email protected]> wrote in message
> > news:[email protected]...
> > > Tom:
> > >
> > > I tried it on the real thing and most cells came up with #REF!. Thses are
> > > cells that are formulas. They make up about 98% of all cells. ?
> > >
> > >
> > > "Ronbo" wrote:
> > >
> > > > Tom:
> > > >
> > > > Thanks, it works perfect (in my test situation). Implementing it into
> > my
> > > > real situation I realized that the files are in differant directories.
> > How
> > > > do I compensate for that? Also, is there a way to put this routine in
> > to a
> > > > Summary Workbook, rather than having it creating a new book? I would
> > like to
> > > > use the same workbook each month with the layout and macros.
> > > >
> > > > Sorry for all of the questions, but this level of programming is way
> > beyond
> > > > my skills.
> > > > I am soon going to quit for the weekend. How do I get back to this topic
> > on
> > > > Monday?
> > > > Start a new question or come back here?
> > > >
> > > > Again, Thanks a lot (as always) for your help.
> > > >
> > > >
> > > >
> > > > "Tom Ogilvy" wrote:
> > > >
> > > > > > Sub AABB()
> > > > > > Dim sPath As String
> > > > > > Dim v As Variant
> > > > > > Dim bk As Workbook
> > > > > > Dim bkSum As Workbook
> > > > > > Dim i As Long
> > > > > > Dim sh As Worksheet
> > > > > > Dim cell As Range
> > > > > > Dim rng As Range
> > > > > Dim sh as worksheet
> > > > > > sPath = "C:\Documents and Settings\MyDocuments\"
> > > > > > v = Array("WB1.xls", "WB2.xls", "WB3.xls")
> > > > > > Set bk = Workbooks.Open(sPath & v(LBound(v)))
> > > > > > bk.Worksheets.Copy
> > > > > > Set bkSum = ActiveWorkbook
> > > > > for each sh in bkSum.worksheets
> > > > > if lcase(sh.name) <> "sheet3" and lcase(sh.name) <> "sheet7" then
> > > > > application.displayalerts = False
> > > > > sh.Delete
> > > > > application.Displayalerts = True
> > > > > end if
> > > > > Next
> > > > > > bk.Close SaveChanges:=False
> > > > > > For i = LBound(v) + 1 To UBound(v)
> > > > > > Set bk = Workbooks.Open(sPath & v(LBound(v)))
> > > > > > For Each sh In bkSum.Worksheets
> > > > > > For Each cell In sh.UsedRange
> > > > > > If IsNumeric(cell.Value) Then
> > > > > > Set rng = bk.Worksheets(sh.Name).Range(cell.Address)
> > > > > > If IsNumeric(rng.Value) Then
> > > > > > cell.Value = cell.Value + rng.Value
> > > > > > End If
> > > > > > End If
> > > > > > Next
> > > > > > Next
> > > > > > Next
> > > > > > End Sub
> > > > >
> > > > >
> > > > > Adjust names to match your actual situation.
> > > > > --
> > > > > Regards,
> > > > > Tom Ogilvy
> > > > >
> > > > >
> > > > > "Ronbo" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > Tom:
> > > > > >
> > > > > > Thanks alot. Thats cool. It works perfect.
> > > > > >
> > > > > > What would I do if I only wanted to add only Worksheet3 and
> > worksheet7?
> > > > > >
> > > > > > "Tom Ogilvy" wrote:
> > > > > >
> > > > > > > Sub AABB()
> > > > > > > Dim sPath As String
> > > > > > > Dim v As Variant
> > > > > > > Dim bk As Workbook
> > > > > > > Dim bkSum As Workbook
> > > > > > > Dim i As Long
> > > > > > > Dim sh As Worksheet
> > > > > > > Dim cell As Range
> > > > > > > Dim rng As Range
> > > > > > > sPath = "C:\Documents and Settings\MyDocuments\"
> > > > > > > v = Array("WB1.xls", "WB2.xls", "WB3.xls")
> > > > > > > Set bk = Workbooks.Open(sPath & v(LBound(v)))
> > > > > > > bk.Worksheets.Copy
> > > > > > > Set bkSum = ActiveWorkbook
> > > > > > > bk.Close SaveChanges:=False
> > > > > > > For i = LBound(v) + 1 To UBound(v)
> > > > > > > Set bk = Workbooks.Open(sPath & v(LBound(v)))
> > > > > > > For Each sh In bkSum.Worksheets
> > > > > > > For Each cell In sh.UsedRange
> > > > > > > If IsNumeric(cell.Value) Then
> > > > > > > Set rng = bk.Worksheets(sh.Name).Range(cell.Address)
> > > > > > > If IsNumeric(rng.Value) Then
> > > > > > > cell.Value = cell.Value + rng.Value
> > > > > > > End If
> > > > > > > End If
> > > > > > > Next
> > > > > > > Next
> > > > > > > Next
> > > > > > > End Sub
> > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > > Regards,
> > > > > > > Tom Ogilvy
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "Ronbo" <[email protected]> wrote in message
> > > > > > > news:[email protected]...
> > > > > > > > I have 3 workbooks (and adding), with 20 worksheets each, with
> > all
> > > > > > > workbooks
> > > > > > > > and worksheets laid out exactly the same, I want a summary
> > workbook
> > > > > that
> > > > > > > > would add each worksheet from the 3 workbooks together so that
> > the
> > > > > summary
> > > > > > > > workbook would have 20 worksheets exactly the same as the 3
> > originals.
> > > > > > > > i.e.C:\Documents and Settings\My
> > > > > > > Documents\[WB1.xls]Sheet1'!A1+C:\Documents
> > > > > > > > and Settings\My Documents\[WB2.xls]Sheet1'!A1+'C:\Documents and
> > > > > SettingsMy
> > > > > > > > Documents[WB3.xls]Sheet1'!A1.
> > > > > > > >
> > > > > > > > I know that I can copy this across and down, but I do not want
> > to do
> > > > > it
> > > > > > > that
> > > > > > > > way because it is very time consuming to add a new workbook and
> > it
> > > > > won't
> > > > > > > be
> > > > > > > > long before I run out of character space in the formula.
> > > > > > > >
> > > > > > > > I checked out Ron de Bruins site, but I did not find anything
> > that
> > > > > adds
> > > > > > > the
> > > > > > > > sheet together.
> > > > > > > >
> > > > > > > > I am looking for a way to add the workbooks or worksheets
> > together and
> > > > > > > > easily add a new workbook. Any help or suggestions would be
> > > > > appreciated.
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> >
> >
> >