Hi All

Heres the picture - we have 300 outlets where we need to perform a stock
count. This is done manually from a preprinted sheet(s) and then submitted by
post back to HO. Somehow (and quickly) this information must be put into a
database. My skill level is not that great in Access so I was wondering about
doing the data input via Excel. So I thought that if i have one workbook for
each store and say one worksheet within each workbook where we can input the
data into the file then this would be both very visible for the inputter and
pretty quick. The worksheet could look like the stock count form so its easy
to know what data to put where. Each workbook would be identical except for
each being a different stores data. The inputters could then open the
workbook, add the data and then save it. However I would then need to
consolidate it all into either one massive workbook or somehow combine it
into some form of database. So the question is what is/would be the best way
to consolidate this data really quickly? I know pivot tables are built for
this sort of thing but whether one could consolidate 300 files I dont know
and I guess thats quite a bore to do.

Over to you gurus for some thoughts

Thanks

Reggiee

Reggiee,

A macro would be the best way - very quick, with little work on your part, and lots of code in my
examples workbook on which to base your macro.

You could make a single database from the files very easily IF, and this is a big IF, there are
fewer than 218 items in your database. Otherwise, you would need to use multiple databases, and use
a pivot table with consolidation ranges. (With Excel 2007, you wouldn't have that limitation
because of the greater number of rows.)

Let us know how many items in the stock count, and we can help you with a macro that will create the
database(s) for the PivotTable.

HTH,
Bernie
MS Excel MVP


"Reggiee" wrote in message
...
Hi All

Heres the picture - we have 300 outlets where we need to perform a stock
count. This is done manually from a preprinted sheet(s) and then submitted by
post back to HO. Somehow (and quickly) this information must be put into a
database. My skill level is not that great in Access so I was wondering about
doing the data input via Excel. So I thought that if i have one workbook for
each store and say one worksheet within each workbook where we can input the
data into the file then this would be both very visible for the inputter and
pretty quick. The worksheet could look like the stock count form so its easy
to know what data to put where. Each workbook would be identical except for
each being a different stores data. The inputters could then open the
workbook, add the data and then save it. However I would then need to
consolidate it all into either one massive workbook or somehow combine it
into some form of database. So the question is what is/would be the best way
to consolidate this data really quickly? I know pivot tables are built for
this sort of thing but whether one could consolidate 300 files I dont know
and I guess thats quite a bore to do.

Over to you gurus for some thoughts

Thanks

Reggiee

Thanks Bernie but gut feel is that were heading over 250 items to count -
however would love to see some of your code to see the principles behiind it
to see if i could adapt it somehow. I guess the other option is to write a
macro which effectively copies and pastes the data into an Access database?

Reggiee

"Bernie Deitrick" wrote:

Reggiee,

A macro would be the best way - very quick, with little work on your part, and lots of code in my
examples workbook on which to base your macro.

You could make a single database from the files very easily IF, and this is a big IF, there are
fewer than 218 items in your database. Otherwise, you would need to use multiple databases, and use
a pivot table with consolidation ranges. (With Excel 2007, you wouldn't have that limitation
because of the greater number of rows.)

Let us know how many items in the stock count, and we can help you with a macro that will create the
database(s) for the PivotTable.

HTH,
Bernie
MS Excel MVP


"Reggiee" wrote in message
...
Hi All

Heres the picture - we have 300 outlets where we need to perform a stock
count. This is done manually from a preprinted sheet(s) and then submitted by
post back to HO. Somehow (and quickly) this information must be put into a
database. My skill level is not that great in Access so I was wondering about
doing the data input via Excel. So I thought that if i have one workbook for
each store and say one worksheet within each workbook where we can input the
data into the file then this would be both very visible for the inputter and
pretty quick. The worksheet could look like the stock count form so its easy
to know what data to put where. Each workbook would be identical except for
each being a different stores data. The inputters could then open the
workbook, add the data and then save it. However I would then need to
consolidate it all into either one massive workbook or somehow combine it
into some form of database. So the question is what is/would be the best way
to consolidate this data really quickly? I know pivot tables are built for
this sort of thing but whether one could consolidate 300 files I dont know
and I guess thats quite a bore to do.

Over to you gurus for some thoughts

Thanks

Reggiee

Reggiee,

The macro below should be copied into a codemodule of an otherwise blank workbook. Save the
workbook into the same folder as your other files with the database, then run the macro. The
assumptions are that the databases are contiguous single tables starting in cell A1, with no blanks
in column A. The databases created use the filename and sheet names as additional fields.

HTH,
Bernie
MS Excel MVP

Option Explicit
Sub Consolidate()
' Written by Bernie Deitrick
' Will consolidate Mulitple Sheets
' from Multiple Files onto one or more sheets
' Assumes that all data starts in cell A1 and
' is contiguous, with no blanks in column A
Dim wkShtCnt As Integer
Dim HeadersDone As Boolean
Dim Basebook As Workbook
Dim myBook As Workbook
Dim mySheet As Worksheet
Dim i As Integer

HeadersDone = False

wkShtCnt = 1
With Application
.DisplayAlerts = False
.EnableEvents = False
.ScreenUpdating = False
End With

With Application.FileSearch
.NewSearch
'Change this to your directory if you haven't
'saved this file in the folder of interest
.LookIn = ThisWorkbook.Path
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
Set Basebook = ThisWorkbook
For i = 1 To .FoundFiles.Count
If .FoundFiles(i) < ThisWorkbook.FullName Then
Set myBook = Workbooks.Open(.FoundFiles(i))
For Each mySheet In myBook.Worksheets
mySheet.Activate
If HeadersDone Then
Range("A1").CurrentRegion.Offset(1).Copy _
Basebook.Worksheets(wkShtCnt).Range("C65536").End( xlUp).Offset(1, 0)
Else
Range("A1").CurrentRegion.Copy _
Basebook.Worksheets(wkShtCnt).Range("C65536").End( xlUp).Offset(1, 0)
End If
With Basebook.Worksheets(wkShtCnt)
.Range(.Range("A65536").End(xlUp).Offset(1, 0), _
.Range("C65536").End(xlUp).Offset(0, -2)).Value = _
myBook.Name
.Range(.Range("B65536").End(xlUp).Offset(1, 0), _
.Range("C65536").End(xlUp).Offset(0, -1)).Value = _
mySheet.Name
End With
If Not HeadersDone Then
Basebook.Worksheets(wkShtCnt).Range("A2").Value = "File Name"
Basebook.Worksheets(wkShtCnt).Range("B2").Value = "Sheet Name"
HeadersDone = True
End If

Next mySheet
myBook.Close
If Basebook.Worksheets(wkShtCnt).Range("C65536").End( xlUp).Row 50000 Then
Basebook.Worksheets.Add after:=Basebook.Worksheets(wkShtCnt)
wkShtCnt = wkShtCnt + 1
HeadersDone = False
End If
End If
Next i
End If
End With

With Application
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With

Basebook.SaveAs Application.GetSaveAsFilename


End Sub






"Reggiee" wrote in message
...
Thanks Bernie but gut feel is that were heading over 250 items to count -
however would love to see some of your code to see the principles behiind it
to see if i could adapt it somehow. I guess the other option is to write a
macro which effectively copies and pastes the data into an Access database?

Reggiee

"Bernie Deitrick" wrote:

Reggiee,

A macro would be the best way - very quick, with little work on your part, and lots of code in my
examples workbook on which to base your macro.

You could make a single database from the files very easily IF, and this is a big IF, there are
fewer than 218 items in your database. Otherwise, you would need to use multiple databases, and
use
a pivot table with consolidation ranges. (With Excel 2007, you wouldn't have that limitation
because of the greater number of rows.)

Let us know how many items in the stock count, and we can help you with a macro that will create
the
database(s) for the PivotTable.

HTH,
Bernie
MS Excel MVP


"Reggiee" wrote in message
...
Hi All

Heres the picture - we have 300 outlets where we need to perform a stock
count. This is done manually from a preprinted sheet(s) and then submitted by
post back to HO. Somehow (and quickly) this information must be put into a
database. My skill level is not that great in Access so I was wondering about
doing the data input via Excel. So I thought that if i have one workbook for
each store and say one worksheet within each workbook where we can input the
data into the file then this would be both very visible for the inputter and
pretty quick. The worksheet could look like the stock count form so its easy
to know what data to put where. Each workbook would be identical except for
each being a different stores data. The inputters could then open the
workbook, add the data and then save it. However I would then need to
consolidate it all into either one massive workbook or somehow combine it
into some form of database. So the question is what is/would be the best way
to consolidate this data really quickly? I know pivot tables are built for
this sort of thing but whether one could consolidate 300 files I dont know
and I guess thats quite a bore to do.

Over to you gurus for some thoughts

Thanks

Reggiee

thanks Bernie

Quite neat that - I think the workbooks may have some empty cells in there
but am sure i can add some code to delete these cells

Thanks again

reggiee

"Bernie Deitrick" wrote:

Reggiee,

The macro below should be copied into a codemodule of an otherwise blank workbook. Save the
workbook into the same folder as your other files with the database, then run the macro. The
assumptions are that the databases are contiguous single tables starting in cell A1, with no blanks
in column A. The databases created use the filename and sheet names as additional fields.

HTH,
Bernie
MS Excel MVP

Option Explicit
Sub Consolidate()
' Written by Bernie Deitrick
' Will consolidate Mulitple Sheets
' from Multiple Files onto one or more sheets
' Assumes that all data starts in cell A1 and
' is contiguous, with no blanks in column A
Dim wkShtCnt As Integer
Dim HeadersDone As Boolean
Dim Basebook As Workbook
Dim myBook As Workbook
Dim mySheet As Worksheet
Dim i As Integer

HeadersDone = False

wkShtCnt = 1
With Application
.DisplayAlerts = False
.EnableEvents = False
.ScreenUpdating = False
End With

With Application.FileSearch
.NewSearch
'Change this to your directory if you haven't
'saved this file in the folder of interest
.LookIn = ThisWorkbook.Path
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
Set Basebook = ThisWorkbook
For i = 1 To .FoundFiles.Count
If .FoundFiles(i) < ThisWorkbook.FullName Then
Set myBook = Workbooks.Open(.FoundFiles(i))
For Each mySheet In myBook.Worksheets
mySheet.Activate
If HeadersDone Then
Range("A1").CurrentRegion.Offset(1).Copy _
Basebook.Worksheets(wkShtCnt).Range("C65536").End( xlUp).Offset(1, 0)
Else
Range("A1").CurrentRegion.Copy _
Basebook.Worksheets(wkShtCnt).Range("C65536").End( xlUp).Offset(1, 0)
End If
With Basebook.Worksheets(wkShtCnt)
.Range(.Range("A65536").End(xlUp).Offset(1, 0), _
.Range("C65536").End(xlUp).Offset(0, -2)).Value = _
myBook.Name
.Range(.Range("B65536").End(xlUp).Offset(1, 0), _
.Range("C65536").End(xlUp).Offset(0, -1)).Value = _
mySheet.Name
End With
If Not HeadersDone Then
Basebook.Worksheets(wkShtCnt).Range("A2").Value = "File Name"
Basebook.Worksheets(wkShtCnt).Range("B2").Value = "Sheet Name"
HeadersDone = True
End If

Next mySheet
myBook.Close
If Basebook.Worksheets(wkShtCnt).Range("C65536").End( xlUp).Row 50000 Then
Basebook.Worksheets.Add after:=Basebook.Worksheets(wkShtCnt)
wkShtCnt = wkShtCnt + 1
HeadersDone = False
End If
End If
Next i
End If
End With

With Application
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With

Basebook.SaveAs Application.GetSaveAsFilename


End Sub






"Reggiee" wrote in message
...
Thanks Bernie but gut feel is that were heading over 250 items to count -
however would love to see some of your code to see the principles behiind it
to see if i could adapt it somehow. I guess the other option is to write a
macro which effectively copies and pastes the data into an Access database?

Reggiee

"Bernie Deitrick" wrote:

Reggiee,

A macro would be the best way - very quick, with little work on your part, and lots of code in my
examples workbook on which to base your macro.

You could make a single database from the files very easily IF, and this is a big IF, there are
fewer than 218 items in your database. Otherwise, you would need to use multiple databases, and
use
a pivot table with consolidation ranges. (With Excel 2007, you wouldn't have that limitation
because of the greater number of rows.)

Let us know how many items in the stock count, and we can help you with a macro that will create
the
database(s) for the PivotTable.

HTH,
Bernie
MS Excel MVP


"Reggiee" wrote in message
...
Hi All

Heres the picture - we have 300 outlets where we need to perform a stock
count. This is done manually from a preprinted sheet(s) and then submitted by
post back to HO. Somehow (and quickly) this information must be put into a
database. My skill level is not that great in Access so I was wondering about
doing the data input via Excel. So I thought that if i have one workbook for
each store and say one worksheet within each workbook where we can input the
data into the file then this would be both very visible for the inputter and
pretty quick. The worksheet could look like the stock count form so its easy
to know what data to put where. Each workbook would be identical except for
each being a different stores data. The inputters could then open the
workbook, add the data and then save it. However I would then need to
consolidate it all into either one massive workbook or somehow combine it
into some form of database. So the question is what is/would be the best way
to consolidate this data really quickly? I know pivot tables are built for
this sort of thing but whether one could consolidate 300 files I dont know
and I guess thats quite a bore to do.

Over to you gurus for some thoughts

Thanks

Reggiee

Reggiee,

You're welcome.

Blanks are OK in your data, as long as there are no fully blank lines, and none at the bottom on
column A. Since Column A is, presumably, your identifiers, it should be fully filled in.

HTH,
Bernie
MS Excel MVP


"Reggiee" wrote in message
...
thanks Bernie

Quite neat that - I think the workbooks may have some empty cells in there
but am sure i can add some code to delete these cells

Thanks again