Summary on one page

B

Box 666

At the moment I have over 40 workbooks sent to me each week, each workbook
has 3 -4 worksheets in it detailing the previous weeks work. I then have to
open each workbook and then go to each worksheet and copy the details fom
the previous week (approx 10 rows) and paste them into a single workbook
(which summaries the various workbook) Can the above be carried out by some
f orm of macro. or programming (am I in the correct forum?)

My issues seem to be
How do I select the correct information from the correct worksbook / sheet.
This should not be too bad as the new weeks work is always in the same
position each week. ie from A3 to L12. Although on the next worksheet it may
be A5 to M16 (but it will always be in those positions on each week)

The second part of the probelem is how to add it to the bottom of the new
summaried sheet each week.

Any help greatfully received.

Bob
 
D

DavidC

First you need a routine to open the required workbook.
The next routine will be to select the data from that
workbook.
Then past that data into the summary workbook starting at
the desired cell.
It will be necessary to find the last row of data and
paste the next lot of data into the row below the last row
of data.
Each book being copied from will need to go through the
same routine, but it may be necessary to write the code
for each instance of workbook separately if the range of
data changes from one book to another.

The sort of commands would be:

Sub sum_sheet()

Worksheets("name of your summary sheet").Visible = True
Sheets("name of your summary sheet").Select
Range("A4").Select (Change A4 to whatever is the first
cell you want the first set of data to be pasted into.)

For a=1 to n (where n is the number of workbooks you need
to get the data from)
Get_data 'Get data from workbook1

budgetobject.Sheets("name of sheet in workbook where
the data is coming from.").Range("A1:L12").Copy

Workbooks("name of your summary workbook").Activate
Sheets("name of the summary sheet for the data").Select

ActiveSheet.Range("A65536").Select
Selection.End(xlUp).Select
R=activecell.row
With Activesheet
..cells(r+1,1).select
activecell.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False

End With

Next a

end sub

Sub get_data()
MsgBox ("Get workbook1")

Dim filetoopen As String

filetoopen = Application _
.GetOpenFilename("XL Files (*.XLS), *.XLS")

Set budgetobject = GetObject(filetoopen)

budgetobject.Application.Visible = True
budgetobject.Parent.Windows(1).Visible = True


End sub

You may need to duplicate much of this code where the
range of cells being copied varies from one book to
another. Then revise this code to cover the range
required:

budgetobject.Sheets("name of sheet").Range("aj1:column
row").Copy

If the ranges do vary then you will need to run the
get_data for each book separately and place the copy
command after each instance.

It is possible but will need some work to achieve it.
Hope this gets you started. I am sure you will have more
questions but lets get you started at least.

BOL
DavidC
 
B

Bob Kilmer

Q. Can the above be carried out by some f orm of macro or programming?
A. Absolutely, yes. Fairly easily for someone moderately skilled in Excel
VBA, but it is a tall order for a casual evening.

Q. How do I select the correct information from the correct worksbook /
sheet?
A. The more things are standardized, the easier it will be to do. Workbook
and sheet naming conventions can tell you something about the data - who it
is from, what period it is for, etc. The more the names and location of the
data is standardized, the easier it is to find quickly and reliably, and
with simpler code.

Programmatically, it is relatively easy to loop through a folder of
workbooks, opening each in turn, loop thru each of the worksheets in a
workbook (or call them by name, if known), get data from the worksheet, and
copy it to one or more master or summary sheets in a master workbook. As
with most things, the devil is in the details. Do some planning and to
identify clear goals for each step. Catalog what you know about the data,
the workbook names, the sheet names, how the data is arranged on the sheets,
where the data is to go.

Opening a folder or a drive full of workbooks is well documented on the web.
Search Google Groups. Looping thru worksheets in a workbook is well
documented on the web. Copying data from worksheet to worksheet, even among
separate workbooks is well documented on the web. Finding ranges of data is
also well documented. Finding the "last cell" so you know where the bottom
of the data is, is well documented on the web. Samples abound.

Q. How to add data to the bottom of the summary sheet?
A. Find the last used row. There are several ways. Very simple ones have
pitfalls and failing under certain conditions (columns with no data, columns
full of data, gaps in data, etc.), but this is well discussed on the web and
you should have no trouble finding it.

Here is one way to go to the last cell in column A of Sheet1 in workbook
Book1.xls.
Application.Goto
Workbooks("Book1.xls").Sheets("Sheet1").Range("A65536").End(xlUp)

Useful searches at http://groups.google.com . (Copy each phrase one at a
time into the seach textbox and search on that phrase.)

consolidate workbooks
loop through workbooks excel
loop through worksheets excel
copy worksheets into new file
copy bottom worksheets
copy usedrange worksheets
find last cell excel
loop through all files in a folder excel
(including MVP in the above searches may get you better quality results)

Here is an example of copying the UsedRange of open Book1.xls, Sheet1 to
open Book2.xls, Sheet1.
Sub Main()
Dim rng As Range
Set rng = Workbooks("Book1.xls").Sheets("Sheet1").UsedRange
rng.Copy Workbooks("Book2.xls").Sheets("Sheet1").Range("A1")
End Sub

Hope this helps get you started.

Bob Kilmer
 
B

Bob Kilmer

Just a tip: one never needs to activate and select ranges to manipulate
Excel programmatically (unless you mean to show the manipulations in the
interface). It usually slows code execution and introduces errors of its
own.

For instance, these two (groups of) statements are equivalent. They return
the (more or less) last row of column A of the named worksheet in the named
workbook.

Workbooks("name").Activate
Sheets("name").Select
ActiveSheet.Range("A65536").Select
Selection.End(xlUp).Select
R = ActiveCell.Row

R = Workbooks("name").Sheets("name").Range("A65536").End(xlUp).Row

Bob Kilmer
 
D

DavidC

Thanks for your tip. I have found it necessary though
when the code is accessing one workbook for data it
sometimes "activates" and so changes the focus and I have
had to then make sure the right book is active to take the
data. I do understand wht you are saying and will review
my code to streamline it.

Regards

DavidC
 
Top