EXCEL - EMAIL MACRO - SUMMARISE DATA AND CREATE TABS FOR EACH UNIQUE VALUES
$30-250 AUD
In corso
Pubblicato più di 10 anni fa
$30-250 AUD
Pagato al completamento
I HAVE ATTACHED THE WORKING EXCEL FILE THAT HAS SOME VBA CODE ALREADY APPLIED THAT IS NOT WORKING JUST RIGHT
THERE ARE 2 TABS (WORKSHEETS)
ONE TAB IS TITLED DATA - THIS IS WHERE ALL THE RAW DATA IS HELD - SO IS WHERE THE MACRO WILL SUMMERISING ITS RESULTS FROM
THE OTHER TAB IS CALLED README - THIS TAB HAS SOME PARAMETERS THE USER CAN APPLY TO FILTER THE DATA HE NEEDS FRO THE DATA TAB
ON THE README TAB THERE IS 5 MACRO BUTTONS - THE FIRST IS CALLED CREATE TAB FOR EACH GROUPNAME - IF THE USER CLICKS ON THIS BUTTON A CODE RUNS THAT LOOKS AT THE RAW DATA IN THE DATA TAB AND CREATES A NEW TAB FOR EACH UNIQUE GROUPNAME - THIS WORKS WELL HOWEVER THE RESULTS FOR THIS GROUPS BY STAFFNAME WHICH IS NOT WHAT I WANT
I NEED THIS CHANGED SO THERE IS NO GROUPING, INSTEAD THE FIRST COLUMN SHOULD BE THE NAME THEN THE STAFF NAME THEN GROUP NAME FOLLOWED BY THE MONTH BY MONTH RESULTS - THERE SHOULD BE NO GROUPING - IT SHOULD BE JUST A LIST
THE NEXT MACRO BUTTON IS CALLED CREATE TAB FOR EACH STAFF NAME - THIS WORKS THE SAME AS THE GROUPNAME BUTTON ONLY THIS WILL CREATE A TAB FOR EACH UNIQUE STAFFNAME - THIS IS WORKING WELL THE RESULTS GROUP BY STAFF NAME WHICH IN THIS CASE IS WHAT I WANT
THE GROUPNAME AND STAFFNAME MACRO BUTTONS CONSIDER THE PARAMETERS PROVIDED
ON THE README TAB THERE IS A START DATE AND END DATE PARAMETER - THE USER WILL KEY IN A START AND END DATE SO WHEN THEY CLICK ON THE GROUPNAME OR STAFF NAME MACRO BUTTONS THE CODE WILL LOOK AT COLUMN E OF THE DATA TAB AND ONLY USE THE DATA WITHIN THE DATE PARAMETER PROVIDED - IT WILL SUMMERISE ALL SALES FOR EACH MONTH AND DISPLAY A TOTAL MONTH SALES MONTH BY MONTH BUT ONLY FOR THOSE MONTHS THAT FALL WITHIN THE DATE PARAMETER PROVIDED
THERE IS ALSO A MAX TRADING MONTHS PARAMETER - WHEN THE GROUPNAME AND STAFFNAME MACRO BUTTONS ARE CLICKED ON IT WILL ALSO CONSIDER THE VALUE KEYED IN FOR THE MAX TRADING MONTHS - CURRENTLY THIS PARAMETER IS NOT WORKING CORRECTLY IN THE CURRENT MACRO BUTTON CODE.
IF I KEY IN 5 INTO THE MAX TRADING MONTHS PARAMETER THE RESULTS SHOULD SUMMERISE ALL SALES FOR EACH MONTH AND DISPLAY A TOTAL MONTH SALES MONTH BY MONTH BUT ONLY FOR THOSE MONTHS THAT FALL WITHIN THE DATE PARAMETER PROVIDED AND ONLY FOR THOSE 'NAMES' THAT HAVE TRADED IN 5 MONTHS OR LESS WITHIN THE DATE PARAMETER PROVIDE
FOR EXAMPLE IF THE DATE PARAMETER PROVIDED WAS 12 MONTHS AND THE MAX TRADING MONTHS WAS 5 THEN ANY 'NAMES' THAT HAVE HAD SALES IN 6 MONTHS OR MORE OUT OF THE 12 MONTH ARE NOT TO BE INCLUDED IN THE RESULTS
FINALLY THERE IS A MAX TRADING VALUE PARAMETER THIS PARAMETER IS ALSO NOT WORKING CORRECTLY IN THE CURRENT CODE OF THE GROUPNAME AND STAFFNAME MACRO BUTTONS
WHEN EITHER OF THE GROUPNAME AND STAFFNAME MACRO BUTTONS ARE CLICKED ON AND A VALUE IS KEYED INTO THE MAX TRADING VALUE PARAMETER WE WANT TO ALSO EXCLUDE THE ‘NAMES’ THAT HAVE GRAND TOTAL SALES GREATER THAN THIS VALUE
SO IF THE PARAMETERS WERE AS AN EXAMPLE
START DATE: MAY-12
END DATE: JUN-13
MAX TRADING MONTHS: 5
MAX TRADING VALUE: 20000
AND THE USER CLICKED ON THE STAFFNAME MACRO BUTTON, THE RESULT WOULD BE
ALL ‘NAMES’ THAT HAVE TRADED NO MORE THAN 5 MONTHS OUT OF 12 MONTHS AND HAVE NOT HAD GRAND TOTAL SALES EXCEEDING $20000.00
ANOTHER GOOD VALUE WOULD BE TO PUT A 0 IN THE MAX TRADING MONTHS PARAMETER AND THE RESULTS WOULD ONLY SHOW THOSE ‘NAMES’ OR ‘GROUPNAMES’ (DEPENDING ON WHAT MACRO BUTTON THE USER CLICKED ON) THAT HAVE NOT TRADED AT ALL AND HENCE HAVE HAD NO SALES
THE MACRO BUTTON TITLED EMAIL STAFFNAMES IS TO BE USED AFTER THE USER HAS CLICKED ON THE CREATE TAB FOR EACH STAFFNAME MACRO BUTTON. THIS MACRO BUTTON SHOULD EMAIL THE CORRESPONDING STAFF NAME TAB TO THE EMAIL ADDRESS THAT IS ASSIHNED TO THE EMAIL ADDRESS TABLE ON THE README TAB – THIS IS CURRENTLY NOR WORKING PROPERLY
CLICK THE RESET MACRO BUTTON TO START AGAIN