Workflow Improvement

Lehel

New Member
Joined
Mar 3, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello there.

I have a small project that should help out my Call Center Department. The project consists of discarding the current "pen and paper" system and implement an new excel based system
for work outside of our Internal CRM. Example : How many calls did the agent take for the inbound line , how many e-mails were answered / sent , etc etc. At the moment for our Internal CRM
I have recently developed an excel macro that will query our SQL-Database for who worked on what and is working great.
However I now would like to build the following macro.

Given a main destination file(which resides on a folder on our network) where all the work outside of the CRM will be stored the structure of the destination excel is as follows
The destination file has 13 Sheets . One sheet for each month, January, February , March , etc etc and a 13-th sheet that keeps track of all the work that was done for controlling purpouses.
For each sheet between January and December I have the following information stored
For January Cells A2 , A11 A20 and A29 contain employee names. The cells between the beforementioned contain the rows for each type of work that is done / will be done .
Example :

Employee Name 01/01/2020 02/01/2021 etc ( On this line I have a filter for working days only for each of the 12 sheet months)
E-mails
Outbound Calls
Inbound Calls
Post(this includes several things that and is tracked only as per hours worked not per item done)

Now my issue comes as follows :
I already created a source file excel with the before mentioned tasks done as button counters with + and -( in case they click too many times or it lags or whatever).
I would need to send this information to the network based destination file on a dayli basis and the code should be able to tell where to put the data.
Example for today's date of 03/03/2021 if I push the Send Data to destination file it should do the following in the before mentioned destination file structure.

Let's say I worked on 30 emails took 15 inbound calls , did 5 outbound calls and spent an hour doing Post.
I click each according button to the specified number each time I have completed an email / inbound / outbound / hour spent doing post.
So I would have the following informationin the source excel file.


Emails 30
Outbound Calls 5
Inbound Calls 15
Post 1

Once I click the send data button to the destination file the code should be able to tell what day is it today . Search for my name , find the according column with what day is it today
and fill out the before mentioned data from the source file in to the destination file.

I got as far as to creating the buttons with the + and - working ( not hard actually ). Where I am stuck is the send data button.
Bellow is the VBA Code.
Thank you!


VBA Code:
Private Sub DataSend_Click()
szToday = Format(Now, "dd-mm-yy-hh-mm-ss")
Dim mySource As Variant
Dim myDest As Variant
mySource = Array("Path to source file")
myDest = Array("Path to destination file")

Dim email As String
Dim Briefe As String
Dim Inbound As String
Dim Outbound As String

email = Range("I4")
Briefe = Range("I7")
Inbound = Range("I10")
Outbound = Range("I13")
' Januar
Dim janStart As String
Dim janEnd As String
janStart = "01-01-2021"
janEnd = "31-01-2021"
If szToday >= janStart Then


' Februar
Dim feb As String
feb = Date
' März
Dim mar As String
mar = Date
' April
Dim apr As String
apr = Date
' May
Dim may As String
may = Date
' Juni
Dim jun As String
jun = Date
' July
Dim jul As String
jul = Date
' August
Dim aug As String
aug = Date
' September
Dim sep As String
sep = Date
' Oktober
Dim okt As String
okt = Date
' November
Dim nov As sring
nov = Date
' Dezember
Dim dez As String
dez = Date

Dim myDataSource As Workbook
Dim myDataComplete As Workbook
Set myDataSource = Workbooks.Open("PathToDestinationFile")

Workbooks("MA-Auswertung.xlsx").Close SaveChanges:=False
End Sub

Private Sub Email_Click()
Dim x As Integer
x = Range("I4").Value
Range("I4").Value = x + 1
End Sub

Private Sub EmailMinus_Click()
Dim x As Integer
x = Range("I4").Value
Range("I4").Value = x - 1
End Sub

Private Sub Briefe_Click()
Dim x As Integer
x = Range("I7").Value
Range("I7").Value = x + 1
End Sub

Private Sub BriefeMinus_Click()
Dim x As Integer
x = Range("I7").Value
Range("I7").Value = x - 1
End Sub


Private Sub Inbound_Click()
Dim x As Integer
x = Range("I10").Value
Range("I10").Value = x + 1
End Sub

Private Sub InboundMinus_Click()
Dim x As Integer
x = Range("I10").Value
Range("I10").Value = x - 1
End Sub

Private Sub Outbound_Click()
Dim x As Integer
x = Range("I13").Value
Range("I13").Value = x + 1
End Sub

Private Sub OutboundMinus_Click()
Dim x As Integer
x = Range("I13").Value
Range("I13").Value = x - 1
End Sub
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Lehel

New Member
Joined
Mar 3, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
For those wondering how this was done, this is the following formulae that came in really handy for this task

=IF(CellWithDate = Today();'[CellToReadDataFromDifferentFile]';CellWithDate)

CellWithDate = 04/03/2021 ( I used this format)

This formulae will read throught the day the data from the source file from the employee and when the day is over it will keep the last value.
No Macro needed for this file at all.

Note: Figured I will not be one of those people that asks a question then gets an answer later on and never answers because "I figured it out".
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,133
Messages
5,622,904
Members
415,938
Latest member
Walkie1994

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top