Updating worksheets

[Bill Carpenter]

I am a Division Sales Manager and here is my problem. Each month I
receive a sales report ( in Excel format) with is approx. 3,500 lines
long by 15 columns wide. It is one big long spreadsheet. The sort
criteria is by Store. So a store can have 40 lines then I insert
lines and total between stores. Easy process. It just took me over 6
hours to break and total each store. That task is complete for July.
Here is my question:

Next month (August) I will receive another spreadsheet with approx.
3,500 lines. I do not want to have to start over. I would like to
"build" off of the july report and update with August numbers. The
variable is the line count will never match from month-to-month.
Meaning that for a particular store the line counts will always be
different base on current sales activity. Simply stated if line A100
in July is for Tooth paste it will show a sales number. In August
there may be no tooth paste sales and A100 will be something totally
different. So the exact line will never match. SO I can not "match
up" each month and update the figures. I need to be absolutely
accurate in these reports.

Is there a way I can "overlay" the new month and check for
additions/deletions prior to totaling for the new month? I at at a
loss as to how to do this. Thanks for any help anyone can provide.

Bill

 

[JulieD]

Hi Bill

if you can sort on store you can use the data / subtotal options to
automatically insert a line between stores & subtotal for you
1. sort first
2. choose data / subtotal
3. at each change in "store"
4. "sum"
5. tick the appropriate boxes
6. click OK

not quite sure what you're trying to achieve when it comes to august

data / consolidation might be what you're looking for (but try it on a
smaller data set first to see if it does what you want)
or
VLOOKUP
or
Pivot Tables

what are the (generic) titles of your 15 columns?

Cheers
JulieD

 

[jeff]

Hi,Bill,

If you do what JulieD suggests, which is a fantastically
fast process for you, you might then wish to run the
following code to build on last month's numbers.

You'll have to play with the code a bit to suit your
needs - essentially what it does is copies all sheets
from all workbooks in the same directory as your current
workbook, and then goes through worksheet 1 and finds
the store total line matching your current sheet's -
the totals line has JulieD's "StoreName Total" title which
it keys off. it then copies last month's total for
that store and the new sum of both to your main sheet.

Note: you should have only one sheet per workbook; and
you should have only your current workbook and last
month's in the same folder. Play with the code to suite
your needs. Run macro "Matchem"
jeff
===========================
Sub Matchem()
CopySheets
MatchData
End Sub
Private Sub MatchData()
Dim cNextRow As Long
Dim Xst As String
Dim Xfnd As Integer
Dim lastrow As Long
Worksheets(1).Activate
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
On Error GoTo ws_exit:
Application.EnableEvents = False

For j = 2 To lastrow
Range("A" & j).Select
With Selection
Xfnd = InStr(1, Selection, " Total")
If Xfnd Then
Xst = Left(.Value, Xfnd - 1)
NextRow = Worksheets(2).Cells(Rows.Count, "A").End
(xlUp).Row
With Worksheets(2).Range("a1:a" & NextRow)
Set c = .Find(Xst & " Total",
LookIn:=xlValues)
If Not c Is Nothing Then
xfer = c.Offset(0, 1).Value
Selection.Offset(0, 2) = xfer
Selection.Offset(0, 3) = xfer +
Selection.Offset(0, 1)
End If
End With
End If
End With
Next j

ws_exit:
Application.EnableEvents = True
End Sub
Sub CopySheets()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = SaveDriveDir ' or set your own "C:\Data"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Application.ScreenUpdating = False
Set basebook = ThisWorkbook
Do While FNames <> ""
If FNames <> ThisWorkbook.Name Then
Set mybook = Workbooks.Open(FNames)
mybook.Worksheets(1).Copy after:= _
basebook.Sheets(basebook.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = mybook.Name
On Error GoTo 0

mybook.Close False
End If
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

 

[Bill Carpenter]

JulieD,

Thank you for the feedback. Here are the columns I must use:

Account, Store, Sto#, AR, Mo_TOT$, SMLY_TOT$, %+/-YTD, YTD_TOT$,
LYTD_TOT$, %+/- YTD

I will try the sub total options and let you know.

When it comes to August, I basically have to put out the same report
but for a different month. Then Sept, and so on. That is why I am
trying to match up or "overlay" the prior months already completed
model. I spent all the time doing July, so I would like to use that
as my master file.

Jeff: Wow, I don't know how I could ever follow all that and have it
work. Maybe I could work on five lines and then perfect it and build
off of that.

I know there are great Excel Wizzes out there, and I am not one of
them.

Thanks for your help.

Regards,

Bill

 

[Bill Carpenter]

I made a "practice" work book/work sheet and did the "subtotal" you
are right Jeff it was so easy. THANKS so much!. Now I need to work
on this match issue, so I can match up text in the new month from the
prior month.

Thanks again. That's why I like Excel, I knew it had to be so simple,
I just did not know how to do it.

Bill