Logging information across different spreadsheets w/out repitition

Hi welcome to the forum,
Yes almost everything is possible in Excel ... with all the information.
I'm sure you understand what you have and require but putting it into word for others to understand and see the picture, that's a entire other story.
How about a sample of with dummy data for the weeks and a dummy back log .
Will make it easier for someone to pick it up and try and help.
How about mentioning the version of Excel? Also helps for those who want to help

Goodmorning,
Attached file came through.
I just took a quick look and my first point, and please don't take me wrong, is that the vba code you wrote, recorded or copied is a programmer's nightmare

I'll read and see if I can first of all understand what you want to do exactly and see if I can help you out.

Not much I can do without the required macros.

All your macros are in the personal macro book and what you have in the Bookings sheet is unclear

It makes a little sense but I don't see the picture yet.
Could you attach the macro code for the Report so I can test?
And why is it in Personal Xlsb?

Okay, i'll check and see what I can do

I've taken a look at the code.
First thing I see is that the code populating each weekday sheet can be reduced to a loop and maybe 5 lines of code for it to work as it does now.
What I understand is that in Bookings you have the first columns which you copy the corresponding worksheets but you want to be able to use the same names for other days and avoid duplicating these for the same weekday.
The date value you use to determine where in the report the names will go, correct?
Since you clear the worksheets before you start there is almost no way to check what was there in the first place so all data is erased and copied and names that are not used will not be there.
Could you explain that a little more?
I'm busy reducing the code and I see that you only use Monday through Friday en 4 weeks at a time.

Just in case:

Here's the file too

Well this code does exactly the same as the 5-page lines of code you hade.
The only thing you need to do is rename the week sheets, Monday through Friday (withou the numbers to Monday (1) .... Friday (1) (notice the blank between the weekday and the first (

the code is this

Public Sub CreateJobs_Click()
Dim LastRow     As Long
Dim weekLoop    As Integer
Dim wsB         As Worksheet    '*  variable for the Bookings worksheet
Dim wsWD        As Worksheet    '*  variable for the applicable week worksheet week day
Dim wks         As Integer
Dim dys         As Integer
Dim dayName     As String
Dim tSheet      As String
Dim bTable      As Object
Set bTable = Worksheets("Bookings").ListObjects("Table2")
bTable.Range.AutoFilter Field:=6
Application.ScreenUpdating = False
Set wsB = Worksheets("Bookings")
LastRow = wsB.Cells(Rows.Count, "B").End(xlUp).Row
For wks = 1 To numberOfWeeks
    For dys = 1 To numberOfDays
        dayName = Choose(dys, "Monday", "Tuesday", "Wednesday", "Thursday", "Friday")
        tSheet = dayName & " (" & wks & ")"
        Application.StatusBar = tSheet
        Set wsWD = Worksheets(tSheet)
        wsWD.Range("B5", "D450").ClearContents
        With bTable
            .Range.AutoFilter Field:=6
            .Range.AutoFilter Field:=6, Criteria1:=dayName & wks
        End With
        wsB.Range("B2:D450").Copy
        wsWD.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    Next dys
Next wks
ClearFilter_Click
Application.ScreenUpdating = True
Application.StatusBar = False
ClearFilter_Click
End Sub

This does not do anything new like keeping duplicates it's just you macro made efficient and.. I am still waiting for the answer to my question

IMPORTANT: insert a new VBA module and make sure the macros are Public (move the ClearFilter_Click macro to this project and make it public too)

I corrected the could, my mistake

The next step is not yet done
The current names are processed correctly en you get 10 entries overall

True the clearing was the problem, but then this has to be changed to make it work by keeping the values

The Friday is cleared too. There are some things that are not clear to me
The Bookings table has a name and address columns B-D
what can be done is that the moment you that after the create jobs macro has run the column E and F can be cleared allowing for new entries there.
If you enter the same for a next run it will ignore these and clear them later too.
The macro will not clear all and keep the existing.
You will probably have to add a macro to clear all for a new month.
Is this the idea?

The combination customer name and address will always be unique?

Since you have not answered if the name and address combination is unique I have made the assumption it is since you call this person customer.
I came back on my idea about deleting the weekdays and days column contents after processing.
The file now works, duplicates are avoided in the same week.
If you decide to add another day like Bob 1, 3 and you decide on 1, 3, 2 or you just change that to 4 it will add Bob to that weekday 4

I added a button to Clear all data which takes care of clearing the weeks for a new period of 4 weeks.
I have not added comments to the vba code but first give it a run and we'll see.
I deleted all the empty rows of the table just insert a new table row to add no customers (you know how that works with tables, tight?

Have fun with it.

Before you stumble into it, I added some error control and modified the record check

Minor corrections and the hypelinks for the week 1 worksheets whcih was invalid due to worksheet name changes from Monday to Monday (1) etc.

This my last one, there were some more hyperlinks not working and I always like to see some feedback on the worksheet, like the # of jobs created / registered.
I took a look at the report text you posted, well, My guess you'll have to work on that, it's not good , typically a recorded macro and that's it.
It probably does what you want but if something changes you have to make many modifications.
I hope you enjoy the vba code and can make it work for you.
Here goes V2.0

I'm glad I could help, you guys seem to be doing good things down under

Well, I'm glad I could help.
File looks goo, I would however format the report page to acomodate / fit all columns to one page and x tall.
Further more remove the Print Object in the Menu Button, you do not need to see it in the report.
About the Job order, there are no parameters to determine a job order so how do you want to implement it?
You will have to decide what type of job takes priority or other criteria and you will need to have the criteria available to do this.
I would also suggest an extra column in the Bookings table where you can place a number (for example between 1 and 10 ) and sort the table BEFORE creating jobs so that it places them in that order when the filter applies but then again, it will not carry though the already present jobs if you change priority.
This will imply that you will have to go through all the 20 worksheets to check if the priority still stands and only change those whose date is > than the current date.
All exceptions will have to be coded and checked, that'll be quite a challenge, but first you will have to decide on a strategy

It might happen that you have to change a priority on-the-fly because a customer needs to helped before so that has to me changed too.

I have reattached the file you sent with the report layout as I think is handy.

Here's a simple mailing module

Sub SendUpdate()
    Call MailData("subject string", "status file updated", "wherever@somewhere.com")
End Sub

Function MailData(mSubject As String, mMessage As String, Sendto As String, Optional CCto As String)
Dim eSubject As String, EBody As String
Dim app As Object, Itm As Variant
Set app = CreateObject("Outlook.Application")
Set Itm = app.CreateItem(0)
With Itm
.Subject = mSubject
.to = Sendto
 If Not IsMissing(CCto) Then .CC = CCto
.Body = mMessage
' .Save           ' This property is used when you want to saves mail to the Concept folder
 .Display      ' This property is used when you want to display before sending
' .Send         ' This property is used if you want to send without verification
' .Attachments.Add (Filename) ' Must be complete path'and filename if you require an attachment to be included
End With
Set app = Nothing
Set Itm = Nothing
End Function

You will have to include the attachment path and filename which would be the report sheet only.

You will have to fill in the correct emai address here

sendTo = "whoevere@au.com"

The mail be display only and you have to click send.

You can change that in the code by activating the .Send line

Hi, there was an obsolete module that I removed but I noticed that the report text was incorrect too.
I removed it and wrote a simpler version which refreshes the entire report sheet based upon the data found.
Sorry for 'stalking'

Let me know if you need help

About the routing order,
I suggest you add a worksheet with a table that has TWO columns
first column Town second Column Address
When a new address comes along add it
in that way you can filter on column Town and see all the addresses for that town only
I noticed you cannot combine a Customer name with an address because the Customer is not the one at that address but the one that is going to work at that address, so Customer is really an incorrect name for this column.

The you will need to have a table that gets filled on-the-fly for each week day that has all the addresses for that week (Town + address) in the rows as well as in the columns
For each intersection you will have to calculate the distance between those addresses and use the results in this table to set the order in that weekday and repeat that for ever weekday with more than one record

And last but not least you have to also include the address starting point and decide if you want to start with the one further away or nearest the starting point.

There is quite a lot of code to be found to calculate distances use Google Maps that can be used in Excel

Here's one I found that does the calculating

HTML Code: 

I added the option Miles or KM but the code is as downloaded an all credits go to Tim Hall

I used two of your addresses and results in KM and Miles

Using the above mentioned code the following results in attached file.
I used the four found addresses as Origin address

The 0 values (look blank) are either unable to calculate or less than 1 km

If you need help, just post