Commission Sheet auto load into commission log

[jaihawk8]

I don't even know if this is possible, but you never know unless you ask.

My team does the commissions for Sales.

For each deal, the Sales Rep completes a commission sheet, which calculates everything for the deal and at the bottom of the form, lists out specific information that our Commission Analyst has to enter into our commission log. An example is below:

The Commissions Analyst takes this information and manually enters it into the fields with the Yellow Highlighted Column Headers (example below) and then enters the remaining information manually from Salesforce.com:

I am linking examples of the two files. The information in the Sales Rep area goes into the "Rep" tab in the commission log and the information in both the Analyst and S&T Analyst areas go into the Analyst tabs.

The excel file of the commission sheet is attached to the order in Salesforce. I was wondering if there was any way to allow them to open the commission sheet, import the pertinent information into the Commission Log and then just manually enter the information that doesn't come across. I figured that would eliminate manual mistakes of typing in the numbers, etc...

Commission Log

Commission Sheet

Thanks in advance for your help.

 

[Rijnsent]

Hi there,
it sounds like VBA can be of help there. Macros can basically do what they do automatically. You could e.g. code something where they open the one file, start a macro (by e.g. pressing a button), which opens a pop-up to select the "source file" of which the yellow fields then get loaded into their file. It depends a bit on how often this is done whether it's worth building or not. It doesn't sound insanely complex and VBA can be quite a nice skill to learn&have, so it could make for a good first project.
Cheers,
Koen

 

[jaihawk8]

OK, so I tried to give this a try (my VBA skills are pretty rudimentary). I need it to treat the Source Workbook as the file that is open. Our commission people will be opening a different commission sheet for each order, so I want to create a button that they just click on the commission sheet to load it into the log. I don't think I have that part right, but I'm not sure. Anyway, right now it's failing on the bolded red line below:

Sub CopyData()
Dim targetWB As Workbook
Dim repWS As Worksheet
Dim analystWS As Worksheet
Dim sourceRow As Long
Dim targetRowRep As Long
Dim targetRowAnalyst As Long

' Open target workbook
Set targetWB = Workbooks.Open("C:\Users\jbohl\OneDrive - Quadient\Jai's X Drive\Log Testing\Test Commission Log.xlsm")
' Set Rep and Analyst worksheets
Set repWS = targetWB.Sheets("Rep")
Set analystWS = targetWB.Sheets("Analyst")

' Define the start rows in target worksheets
targetRowRep = 2 ' Start from row 2 in Rep tab
targetRowAnalyst = 2 ' Start from row 2 in Analyst tab

' Loop through each row in the source worksheet
For sourceRow = 52 To 73 ' Assuming data is from row 52 to 73 in the source worksheet
If Not IsEmpty(ThisWorkbook.Sheets(1).Cells(sourceRow, "B").Value) Then
' Copy data to Rep tab
With repWS
.Cells(targetRowRep, "F").Value = ThisWorkbook.Sheets(1).Cells(sourceRow, "B").Value
.Cells(targetRowRep, "G").Value = ThisWorkbook.Sheets(1).Cells(sourceRow, "C").Value
.Cells(targetRowRep, "H").Value = ThisWorkbook.Sheets(1).Cells(sourceRow, "D").Value
.Cells(targetRowRep, "I").Value = ThisWorkbook.Sheets(1).Cells(sourceRow, "H").Value
.Cells(targetRowRep, "J").Value = ThisWorkbook.Sheets(1).Cells(sourceRow, "J").Value
.Cells(targetRowRep, "K").Value = ThisWorkbook.Sheets(1).Cells(sourceRow, "O").Value
.Cells(targetRowRep, "L").Value = ThisWorkbook.Sheets(1).Cells(sourceRow, "P").Value
.Cells(targetRowRep, "M").Value = ThisWorkbook.Sheets(1).Cells(sourceRow, "Q").Value
.Cells(targetRowRep, "N").Value = ThisWorkbook.Sheets(1).Cells(sourceRow, "R").Value
.Cells(targetRowRep, "O").Value = ThisWorkbook.Sheets(1).Cells(sourceRow, "V").Value
.Cells(targetRowRep, "P").Value = ThisWorkbook.Sheets(1).Cells(sourceRow, "W").Value
.Cells(targetRowRep, "Q").Value = ThisWorkbook.Sheets(1).Cells(sourceRow, "X").Value
.Cells(targetRowRep, "R").Value = ThisWorkbook.Sheets(1).Cells(sourceRow, "AC").Value
End With

' Copy data to Analyst tab
With analystWS
.Cells(targetRowAnalyst, "F").Value = ThisWorkbook.Sheets(1).Cells(sourceRow, "B").Value
.Cells(targetRowAnalyst, "G").Value = ThisWorkbook.Sheets(1).Cells(sourceRow, "C").Value
.Cells(targetRowAnalyst, "H").Value = ThisWorkbook.Sheets(1).Cells(sourceRow, "D").Value
.Cells(targetRowAnalyst, "I").Value = ThisWorkbook.Sheets(1).Cells(sourceRow, "H").Value
.Cells(targetRowAnalyst, "J").Value = ThisWorkbook.Sheets(1).Cells(sourceRow, "J").Value
.Cells(targetRowAnalyst, "K").Value = ThisWorkbook.Sheets(1).Cells(sourceRow, "O").Value
.Cells(targetRowAnalyst, "L").Value = ThisWorkbook.Sheets(1).Cells(sourceRow, "P").Value
.Cells(targetRowAnalyst, "M").Value = ThisWorkbook.Sheets(1).Cells(sourceRow, "Q").Value
.Cells(targetRowAnalyst, "N").Value = ThisWorkbook.Sheets(1).Cells(sourceRow, "R").Value
.Cells(targetRowAnalyst, "O").Value = ThisWorkbook.Sheets(1).Cells(sourceRow, "V").Value
.Cells(targetRowAnalyst, "P").Value = ThisWorkbook.Sheets(1).Cells(sourceRow, "W").Value
.Cells(targetRowAnalyst, "Q").Value = ThisWorkbook.Sheets(1).Cells(sourceRow, "X").Value
.Cells(targetRowAnalyst, "R").Value = ThisWorkbook.Sheets(1).Cells(sourceRow, "AC").Value
End With

' Move to the next row in the target worksheets
targetRowRep = targetRowRep + 1
targetRowAnalyst = targetRowAnalyst + 1
End If
Next sourceRow

' Close target workbook without saving changes
targetWB.Close False
End Sub

 

[Rijnsent]

Hi there,
for starters: could you please put your code in the right CODE brackets ([ CODE=vba][/CODE] ), that makes it much more readable? You rcode looks quitte okay, what happens if you try to step through the code (using F8)? What is the error message you see?
I don't know if 52-73 is are fixed rows or if you have other identifiers to know which lines you need to extract? You could e.g. loop from row 1 to the last filled row and only use the cells with yellow background, like so (untested mockup code):

Set Sht = ActiveSheet
LastRw = Sht.Range("A" & Cells.Rows.Count).End(xlUp).Row
For Rw = 1 To LastRw
    If Sht.Range("A" & Rw).Value <> "" And Sht.Range("A" & Rw).Interior.ColorIndex = 28 Then
    'or use: e.g. .Interior.Color = RGB(255,0,0)
        'do something
    End If
Next Rw

Cheers,
Koen