Your Input Needed!

[John Kitchens]

Hello All,

I need your input. I made program in Excel and all is working great. My
problem:

I need a way to produce a summary of the daily activities. We are using a
seperate file for each transaction.

We are in the recycling business. We buy 25 different items. These are
purchased by weight. On my original SS that is used for each transaction I
have dropdowns where you can select the commodity and put the weight and
through the use of SUMIF and validation it carries the weight to the proper
commodity.

This is an example what I am trying to do:

1st customer of the day brings his items, iron, tin, cans, etc. (could be as
many as 25 different commodities). We save this file as say "t57(081704)".

2nd customer, same deal. File saved as "t58(081704)".

I would like to create a file that I could have all 25 commodites listed and
have the SS user go back in and fill out entering for example, Iron - (put
the weight from the t57(081704) and then from the next ticket.

I am wondering if there is a way that he could go to the cell and just type
in all of the amounts for each commodity. We may have as many as 50 or 60
customers in a day. I need all of this to be on one sheet. Well at least the
summary part.

I am not the person filling out the tickets. This is at an offsite location
with multiple inexperienced computer users, so it has to be as easy to use
as possible.

Can any offer any suggestions?

 

[Pranas Rudys]

Sure sounds like you need Microsoft Access. Your problem is easy in
Access.

Barring the above, suggest you accumulate all "tickets" in one
workbook. If "tickets" are from a template and are copied to the
workbook, it is easy to set up a Summary sheet. The summary sheet
would sum all the cells in the worksheets which represent totals.

 

[Dave Peterson]

Are all the daily files stored in the same folder?

Are all the 25 "keys" and 25 "values" stored in the same location?

Is the name of the worksheet consistent between workbooks?

If yes to all of these, then I used this:

Option Explicit
Sub testme01()

Dim myFiles() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim tempWkbk As Workbook
Dim RptWks As Worksheet
Dim RngToCopy As Range
Dim DestCell As Range
Dim myDateStr As String

myDateStr = InputBox(prompt:="Enter the Date Required as mmddyy <8 digits!>", _
Default:=Format(Date, "mmddyy"))

If Len(myDateStr) <> 6 _
Or IsNumeric(myDateStr) = False Then
MsgBox "Please try later!"
Exit Sub
End If

'change to point at the folder to check
myPath = "c:\my documents\excel\test"
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

myFile = Dir(myPath & "*.xls")
If myFile = "" Then
MsgBox "No files found."
Exit Sub
End If

Application.ScreenUpdating = False

Set RptWks = Workbooks.Add(1).Worksheets(1)
With RptWks
.Range("a1").Resize(1, 3).Value _
= Array("Workbook Name", "Key", "Value")
Set DestCell = .Range("b2")
End With

'get the list of files
fCtr = 0
Do While myFile <> ""
If LCase(myFile) Like "t##(" & myDateStr & ").xls" Then
fCtr = fCtr + 1
ReDim Preserve myFiles(1 To fCtr)
myFiles(fCtr) = myFile
End If
myFile = Dir()
Loop

If fCtr > 0 Then
For fCtr = LBound(myFiles) To UBound(myFiles)
Application.StatusBar = "Processing: " & myFiles(fCtr)
Set tempWkbk = Workbooks.Open(Filename:=myPath & myFiles(fCtr))
Set RngToCopy = Nothing
On Error Resume Next
Set RngToCopy = tempWkbk.Worksheets("sheet1").Range("a1:b25")
On Error GoTo 0
If RngToCopy Is Nothing Then
MsgBox "no sheet1 in: " & tempWkbk.FullName
Else
RngToCopy.Copy
DestCell.PasteSpecial Paste:=xlPasteValues
DestCell.Offset(0, -1).Resize(RngToCopy.Rows.Count).Value _
= tempWkbk.FullName
Set DestCell = DestCell.Offset(RngToCopy.Rows.Count)
End If
tempWkbk.Close savechanges:=False
Next fCtr
End If

With RptWks
.UsedRange.Columns.AutoFit
With .Range("a:C")
.Sort key1:=.Columns(1), order1:=xlAscending, header:=xlYes
End With
End With

With Application
.ScreenUpdating = True
.StatusBar = False
End With

End Sub

I assumed that the keys and values were in A1:B25 of a worksheet named Sheet1
and I used this folder:
"c:\my documents\excel\test"

You could create a new workbook with this macro in it. Plop a big old giant
button from the forms toolbar on the first worksheet and assign this macro to
that button.

Then the end users can open this workbook, click the giant button and answer a
the date question.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm