How to Create Summary Page From Multiple Sheets in Multiple Books? (Excel 2003)

[parecon]

I'm looking for assistance in creating a monthly summary page of the same cells from different worksheets (Sheet1!D25, Sheet3!D26) from different workbooks (June 1/11, June 2/11) all in the same folder. The cell Sheet1!D25 is the sum of a list, if that matters. Each workbook corresponds to one day of the month.

The summary page would look like this with a row for each day of the month (sorry but I can't download the html maker at work ):

<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=256 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>Date</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Total 1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Total 2</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Total 3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="40695">01-Jun-11</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>

So, on the summary page "Total 1" would include the info in Sheet1!D25 for June 1/11, and "Total 2" would include the info in Sheet3!D25 for June 1/11, etc. In addition, on the summary page June 2/11 would list the same info, and so on for the entire month.

I would appreciate any help if there is a way to accomplish this more efficiently on Excel 2003?
-Thanks

 

[John_w]

Use the Dir function to loop through the files in the folder and, for each file, do a Workbooks.Open and retrieve the values from the relevant cells and populate the relevant row in the summary sheet. Do a File - Open with the macro recorder to generate the code for the Workbooks.Open. The Dir Help has a loop example.

What are the file names of the workbooks? Since the Dir function doesn't guarantee the order in which the files are retrieved, you would need to parse each file name to determine which date it belongs to, or if there is a cell which contains the date of that workbook use that instead.

 

[parecon]

Thanks for the reply. The name of the workbooks are June 1-11, June 2-11, etc., so one workbook for each day of the month.

Also, unfortunately, I'm not familiar with VBA or macros, but I'm definitely willing to learn. Is there a resource for going through the steps you outlined? Also, where would I find the Dir Help?

Thanks,
Tony

 

[John_w]

The Macro Recorder is Tools - Macro - Record new macro, from any Excel worksheet menu bar. Do the manual steps (File - Open - browse to and open a workbook) with the recorder running, then stop the recorder.

To open Visual Basic, click Tools - Macro - Visual Basic Editor. Or press Alt+F11. Or right-click on a sheet tab and click View Code. You should see a module named Module1; double-click that to open the code generated by the macro recorder.

In the Visual Basic editor, type "Dir" without the quotes in the pane on the right and press F1 to show help on that keyword. Or click Help - Microsoft Visual Basic Help and enter Dir in the search box.

Tip - in the Visual Basic Editor, click Tools - Options. Make sure 'Require Variable Declaration' is ticked. This automatically puts Option Explicit at the top of every module and forces you to declare all variables, which is essential for both novice and experienced programmers.

Google "learn VBA" or "VBA tutorial" for online guides.

For your specific request, there are many similar examples in this forum (http://www.mrexcel.com/forum/showthread.php?t=548613&highlight=dir+loop+workbooks.open is one - not exactly what you want, but a suitable starting point). I could write the code for you, but you'll learn more by having a go yourself, maybe by tweaking an existing example.

 

[parecon]

Thanks for the info. I would much rather understand how to construct the code myself than just copy and paste.

You've given me a great jumping off point which is where I'll start. I'll continue to post if I have issues. Hopefully this thread will help other novices.

Thanks

 

[parecon]

I'm not sure if I should start a new thread, but at John's advice I googled learn VBA and trying a tutorial.

I'm hoping someone can tell me why I get a "Compile error: Variable not defined" message with this code with the first line being highlighted:

Sub DisplayBiggest()
maxWeight = 0
For Each theCell In Range("c3:c12")
If theCell.Value > maxWeight Then
maxWeight = theCell.Value
pigNumber = theCell.Offset(0, -1).Value
End If
Next
MsgBox "The biggest piglet is number: " & pigNumber & " weighing:" & maxWeight
End Sub

This is the data:

<TABLE style="WIDTH: 152pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=203 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17></TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=75></TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Pig Number</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Weight</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>10</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>2</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>6</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>3</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>6</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>4</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>5</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>6</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>9</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>7</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>6</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>8</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>7</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>9</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>9</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>10</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>7</TD></TR></TBODY></TABLE>

 

[John_w]

I'm hoping someone can tell me why I get a "Compile error: Variable not defined" message

Because you have Option Explicit (a good thing) and you haven't declared any of the variables. You can either:

a) Delete the Option Explicit line. Don't do this, as it a very good way of hiding all sorts of errors which only appear at run-time; or

b) Declare the variables with appropriate data types with the Dim statement. Always do Debug - Compile VBAProject, and fix any errors until it compiles successfully. See http://www.cpearson.com/excel/DeclaringVariables.aspx

Try this (option b)):

Option Explicit

Sub DisplayBiggest()

    Dim maxWeight As Integer, pigNumber As Integer
    Dim theCell As Range
    
    maxWeight = 0
    For Each theCell In Range("c3:c12")
        If theCell.Value > maxWeight Then
            maxWeight = theCell.Value
            pigNumber = theCell.Offset(0, -1).Value
        End If
    Next
    MsgBox "The biggest piglet is number: " & pigNumber & " weighing:" & maxWeight
End Sub

 

[parecon]

Thanks John_w. It worked perfectly. I'm going to do some digging to try to work out the code for my original problem. I'll post once I have something based on your original suggestion of using the macro recorder.

 

[parecon]

John_w: I used the macro recorder to capture the code for what I need but I'm a little lost on the dir function:

Sub Junetotals()
'
' Junetotals Macro
' Macro recorded 06/09/2011 by tonyg
'
'

Range("B2").Select
Workbooks.Open Filename:= _

"S:\VOL_Shared\GS\G S\1 Daily Finance\June 2011\1 June 11.xls"
​

Sheets("Sheet 1").Select
Range("D25").Select
Selection.Copy
ActiveWindow.ActivateNext
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False
​

Range("C2").Select
ActiveWindow.ActivateNext
Sheets("Sheet 2").Select
Range("D26").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ActivateNext
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False
​

End Sub

 

[John_w]

As I said, the macro recorder was just to get the Workbooks.Open code - the syntax - one statement.

Here is complete code for your original request:

Option Explicit

Sub Create_Month_Summary()

    Dim folderPath As String
    Dim fileName As String
    Dim thisWorkbook As Workbook
    Dim dayNumber As Integer
    Dim workbookDate As Date
    
    'Folder containing daily dated workbooks for a month - CHANGE AS REQUIRED
    
    folderPath = "C:\Excel\June\"
    
    Cells.ClearContents
    
    Range("A1").Value = "Date"
    Range("B1").Value = "Total 1"
    Range("C1").Value = "Total 2"
    Range("D1").Value = "Total 3"
    
    Set thisWorkbook = ActiveWorkbook
    
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"
    
    fileName = Dir(folderPath & "*.xls")
    Do While fileName <> ""
        
        'Parse file name to get workbook date
        
        workbookDate = CDate(Left(fileName, InStr(fileName, ".") - 1))
        dayNumber = Day(workbookDate)
        
        'Copy data from dated workbook to associated row in summary sheet
        
        Workbooks.Open folderPath & fileName
        With thisWorkbook.Sheets("Sheet1").Range("A1")
            .Offset(dayNumber, 0).Value = workbookDate
            .Offset(dayNumber, 1).Value = Sheets("Sheet1").Range("D25").Value
            .Offset(dayNumber, 2).Value = Sheets("Sheet2").Range("D26").Value
            .Offset(dayNumber, 3).Value = Sheets("Sheet3").Range("D27").Value
        End With
        ActiveWorkbook.Close savechanges:=False
        
        'Get next file name
        
        fileName = Dir
    Loop
    
    MsgBox "Finished"
    
End Sub