Creating a database and PivotTable with a Macro

[Guest]

I need to create a master workbook that reads from several other workbooks each containing a single worksheet. The data from one week will need to be kept for the following week to be compared. I know that a database and a pivottable will probably be the best way to do this, but I'm not familiar enough with writing macros to know how to proceed. Any help would be great.

 

[Tom Ogilvy]

Dim sh as Worksheet
Dim wkbk as Workbook
Dim i as Long, sPath as String
Dim rng as Range
Dim varr as Variant
workbooks("Master.xls").Worksheets.Add
set sh = Activesheet
sPath = "C:\Myfolder\Myfiles\"
varr = Array("bk1.xls", "bk2.xls", . . . , "bk7.xls")
for i = lbound(varr) to ubound(varr)
set wkbk = workbooks.open( sPath & varr(i))
set rng = sh.Cells(rows.count,1).End(xlup)(2)
wkbk.Worksheets(1).Range("A1").Currentregion.Copy _
Destination:=rng
wkbk.close Savechanges:=False
Next

--
Regards,
Tom Ogilvy

I need to create a master workbook that reads from several other workbooks

each containing a single worksheet. The data from one week will need to be
kept for the following week to be compared. I know that a database and a
pivottable will probably be the best way to do this, but I'm not familiar
enough with writing macros to know how to proceed. Any help would be great.

 

[Toby Erkson]

Try this:
http://msdn.microsoft.com/library/d...conworkingwithpivottablepivotchartreports.asp
This one is good:
http://www.microsoft.com/exceldev/articles/movs104.htm

--
Toby Erkson
Oregon, USA
Excel 2002 in Windows XP

I need to create a master workbook that reads from several other workbooks

each containing a single worksheet. The data from one week will need to be
kept for the following week to be compared. I know that a database and a
pivottable will probably be the best way to do this, but I'm not familiar
enough with writing macros to know how to proceed. Any help would be great.