Indirect not the correct solution???

T

Trevor

I have one workbook for each week going back to January 2002. Each
workbook has 13 worksheets making up different locations. Each
worksheet has the exact same template and I need to extract data from
the same 8 cells on each template.
I am trying to build a yearly summary sheet for all the locations
picking up the 8 cells in each weekly sheet. Each workbook is named
"REPORT - Jan 05"; "REPORT - Jan 12" etc and are exacly 7 days apart.
For 2002 I have built a summary sheet which has each location on a
tab, the column heading are the 8 categories from each sheet and the
row headings are the week ending date (or file name). I have tried
using Text and Concatenate functions to be able to use indirect but it
seems as if Excel's resources are not large enough to handle looking
at so many sheets and performing the functions - it takes about 2
minutes to copy one cell.
Can anyone give me a suggestion on whether there is a better way to go
about this or if I should just do a manual entry input (last
solution).

Thanking you in advance.

Trevor
 
B

BrianB

Here is some code that you will need to copy/paste to a code module an
adapt. You may get away with just changing the *Sub Transfer_Data ()
to transfer cell values instead of using copy/paste. eg.
ToSheet. Range("A1").Value = Fromsheet.Range("A1").Value

'==============================================
'- Generic code for transferring data from
'- one or more workbooks in a folder to a master sheet
'-
'- workbooks must be the only ones in the folder
'- run this code from the master book
'-
'----------------------------------------------
Dim ToBook As String
Dim ToSheet As Worksheet
Dim NumColumns As Integer
Dim ToRow As Long
Dim FromBook As String
Dim FromSheet As Worksheet
Dim FromRow As Long
Dim LastRow As Long
'-
'----------------
Sub NEW_MASTER()
'----------------
Application.Calculation = xlCalculationManual
ChDrive ActiveWorkbook.Path
ChDir ActiveWorkbook.Path
ToBook = ActiveWorkbook.Name
Set ToSheet = ActiveWorkbook.Worksheets(1)
NumColumns = ToSheet.Range("A1").End(xlToRight).Column
ToRow = ToSheet.Range("A65536").End(xlUp).Row
'- clear master
If ToRow <> 1 Then
ToSheet.Range(Cells(2, 1), Cells(ToRow
NumColumns)).ClearContents
End If
ToRow = 2
'- main loop
FromBook = Dir("*.xls")
While FromBook <> ""
If FromBook <> ToBook Then
Application.StatusBar = FromBook
Transfer_data
End If
FromBook = Dir
Wend
'-- close
MsgBox ("Done.")
Application.StatusBar = False
Application.Calculation = xlCalculationAutomatic
End Sub
'-------------------------------------------------

Sub Transfer_data()
Workbooks.Open FileName:=FromBook
For Each FromSheet In Workbooks(FromBook).Worksheets
LastRow = FromSheet.Range("A65536").End(xlUp).Row
'- copy paste
FromSheet.Range(Cells(2, 1), Cells(LastRow, NumColumns)).Cop
_
Destination:=ToSheet.Range("A" & ToRow)
'- set next ToRow
ToRow = ToSheet.Range("A65536").End(xlUp).Row + 1
Next
Workbooks(FromBook).Close savechanges:=False
End Sub
'==== EOP =====================================
 
Top