Multiple worksheets - Pivot Table

[RachelSK]

I export my shop sales/stock reports to Excel format. The problem i
that each report sheet is imported as a separate worksheet - so
months sales report may have over 300 worksheets. I have to transfe
the data from each individual worksheet on to one in order t
filter/sort/subtotal the information. At the moment I am using Macro
to cut/paste the information - this has speeded the process up but i
is still very time consuming and I have to record different macros fo
each new type of report. Can I summarize the information from all th
worksheets with a Pivot Table? and if so HOW! - I have tried using th
wizard but I think it assumes I know more than I do! Any help would b
appreciated:confused

 

[jeff]

Hi,

I'm not sure if this will help, but BrianB back in april
posted this code to copy all data on all sheets of
workbooks in the same directory into one master sheet.

If you did this, perhaps the data would be in such
condition for you to use a pivot table to summarize
your data.

Good luck,
jeff

From: "BrianB >" <<[email protected]>
Sent: 4/27/2004 7:52:33 AM



Here is some code that you will need to copy/paste to a
code module and
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
On Error Resume Next
FromSheet.Range(Cells(1, 1), Cells(LastRow,
NumColumns)).Copy _
Destination:=ToSheet.Range("A" & ToRow)
'- set next ToRow
ToRow = ToSheet.Range("A65536").End(xlUp).Row + 1
Next
Workbooks(FromBook).Close savechanges:=False
End Sub