Need Macro Help!

[Brad Rego]

Hello, I use an excel file where I copy and paste the name of a SINGLE sales person and then various formulas use the name of the sales person to reach out to the data source and populate various data points such as sales, revenue, etc.

This calculator works great when running 1-off requests for data by an individual salesperson, but it is only set up to run one salesperson at a time. I now need to run a few hundred of these for every sales person in the company. I do not want to go one by one and copy and paste name after name after name.

Can someone help with a macro that takes a master list of names, runs them through the calculator one at a time and then puts the output into a new excel file with a unique tab for each sales person?

 

[Monty]

Attachment missing.

 

[Monty]

New users Start Here please http://forum.chandoo.org/forums/new-users-please-start-here.14/

 

[Brad Rego]

Forgot to mention, the file is much too large to upload...also it contains NPPI data so I would not be able to upload. I thought perhaps there was a generic macro that could be used to help?

 

[Monty]

Thing is at least a sample file with masked data will help understand your requirement better.

 

[Brad Rego]

Attached is a sample file of what I am trying to do. The first tab is the Source data. The second tab is the Calculator where the sales person's name is entered into the yellow cell (b2) to get the output. The third tab, All Sales People, is the list of all sales people I want to run the calculator for.

I need to run this for hundreds of sales people and would prefer to not do it one at a time. Was looking for a macro that takes the names from the All Sales People tab and one by one runs it through the calculator tab and then creates a new workbook with a unique tab for each salesperson's calculator.

 

[SirJB7]

Hi, Brad Rego!
Names in worksheet Source Data at column A, are unique or repeated?
Regards!

 

[SirJB7]

Hi, Brad Rego!

Just in case they're repeated (otherwise it'd be a trivial situation), give a look at the uploaded file.
Formulas for 3rd worksheet are:

A2: =SI.ERROR(INDICE('Source Data'!A$2:A$13;COINCIDIR(0;CONTAR.SI(A$1:A1;'Source Data'!A$2:A$13);0));"") -----> in English: =IFERROR(INDEX('Source Data'!A$2:A$13,MATCH(0,COUNTIF(A$1:A1,'Source Data'!A$2:A$13),0)),"")

(formula at A2 is an array formula so enter it with Ctrl-Shift-Enter instead of just enter)

B2: =SI($A2="";"";SUMAR.SI('Source Data'!$A$2:$A$13;'All Sales People'!$A2;'Source Data'!B$2:B$13)) -----> in English: =IF($A2="","",SUMIF('Source Data'!$A$2:$A$13,'All Sales People'!$A2,'Source Data'!B$2:B$13))

Copy B2 across to D2, then copy A2:D2 down as required.

Regards!

 

[Brad Rego]

Thank you for your help, but I am looking for a macro that keeps the format of the calculator tab. What I need is 1 worksheet per sales person, not all salespeople on the same worksheet. Any other thoughts?

 

[SirJB7]

Hi, Brad Rego!
Give a look at the uploaded file. This is the VBA code:

Option Explicit

Sub AllTogetherKeepThemSeparated()
    '
    ' constants
    Const ksWSSource = "Source Data"
    Const ksSource = "SourceDataTable"
    Const ksWSCalc = "Calculator"
    Const ksCalc = "CalculatorTable"
    Const ksWBTarget = "Target"
    '
    ' declarations
    Dim wsS As Worksheet, rngS As Range, wsC As Worksheet, rngC As Range, wbT As Workbook
    Dim I As Integer
    '
    ' start
    With ThisWorkbook
        Set wsS = .Worksheets(ksWSSource)
        With wsS
            Set rngS = .Range(ksSource)
        End With
        Set wsC = .Worksheets(ksWSCalc)
        With wsC
            Set rngC = .Range(ksCalc)
        End With
    End With
    Workbooks.Add
    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & ksWBTarget & Format(Now(), "yyyymmddhhmmss") & ".xlsx"
    Set wbT = ActiveWorkbook
    '
    ' process
    For I = 2 To rngS.Rows.Count
        rngS.Cells(I, 1).Copy rngC.Cells(1, 2)
        With wbT.Worksheets
            If I - 1 > .Count Then .Add , wbT.Worksheets(I - 2)
            rngC.Copy wbT.Worksheets(I - 1).[A1]
        End With
    Next I
    '
    ' end
    ActiveWorkbook.Save
    Set wbT = Nothing
    Set rngC = Nothing
    Set wsC = Nothing
    Set rngS = Nothing
    Set wsS = Nothing
    '
End Sub

Regards!

 

[YasserKhalil]

That's great SirJB. I liked your code a lot
As for the copy process, I think it will be better to paste as values so as to break any links between the workbooks .. in that way

rngC.Copy: wbT.Worksheets(i - 1).Range("A1").PasteSpecial xlPasteValues

And it's OP choice at the end ..
Best Regards

 

[SirJB7]

Hi, YasserKhalil!
Thanks for your kind works, feel free to ask anything about it.
OP didn't even answer my question at post #7 so I tried to be as literal as possible. I thought about it but freezing values at certain date should include that date in "the calculator" at least.
Regards!

 

[Brad Rego]

Thank you all for your help..much appreciated!