VBA code to copy raw data into summary table

Hi All,

Could you please assist? I have a bit of excel knowledge but not VBA.

We have a commission sheet of about 400+ rows of each employees commission calculations which are in various categories, such as connections, insurance sales, accessories etc. I want a button to be able to copy only specific values from the rows into a prelim "salary slip" that are in columns format to email to staff.
We work on employee numbers.

From this: (One row per employee)
Emp No Accessory Qty Accessories com OMS Connections Connection Commission
781 23 605.12 44 1 560.00

To this: (One Summary table per employee)
781 R
Contract connections 0 -
Warehouse Connections 0 -
Accessory Sales 0 -


Not sure if it makes sense. There are columns in between that don't need to be copied over.

Basically, I want the program to take the raw data and if it matches that employee number, copy for example data from column I to Accessory Sales, copy value in column P to Contract connections, etc.

Copy from Commission Sheet to Prelim Sheet.

We have already set up Macros to email their prelims, but we copy and paste data manually at the moment for 400+ employees and it's too much work.

Thank you in advance for the assistance.

Hi,

So I did some research and found this code:

Sub SearchForString()

Dim LSearchRow As Integer
Dim LCopyToRow As Integer

On Error GoTo Err_Execute

'Start search in row 2
LSearchRow = 2

'Start copying data to row 2 in Sheet2 (row counter variable)
LCopyToRow = 2

While Len(Range("A" & CStr(LSearchRow)).Value) > 0

'If value in column C = "781", copy entire row to Sheet2
If Range("C" & CStr(LSearchRow)).Value = "781" Then

'Select row in Sheet17 to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy

'Paste row into Sheet18 in next row
Sheets("Sheet18").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

'Move counter to next row
LCopyToRow = LCopyToRow + 1

'Go back to Sheet1 to continue searching
Sheets("Sheet17").Select

End If

LSearchRow = LSearchRow + 1

Wend

'Position on cell A3
Application.CutCopyMode = False
Range("A3").Select

MsgBox "All matching data has been copied."

Exit Sub

Err_Execute:
MsgBox "An error occurred."

End Sub

The problem is that I don't want to copy the entire row, just a certain value in a specific column.

And when I change the employee number and run it again, it pastes over the first row.

I am desperate for help

If anyone can guide me, I would be appreciate it.

Hello JeteMc,

Thank you very much for your reply - I really appreciate you taking the time to help me.

What would you suggest - that we change the headers on the commission sheet?

I really hoped there would be a quicker way to import the data. The problem is that the consultants move to various stores regularly, so we have to keep the sheet updated.

Regards,
April88

Hello,

Thank you very much - you have been such a big help Wish I could buy you coffee or something for your trouble.

I have started changing the header names in the commission sheet and it works wonders.

To answer your move question:
Each store has its own tab: REDS, GROV, JAK, etc (the names don't matter) We copy paste the entire block of the consultant.
For instance, if a consultant moves from REDS to GROV, we copy that persons block and paste in the GROV tab. I checked and the formula stays put, so that's not the problem.

What you could maybe assist with, is the following:
1. Each month has a new commission sheet - how will I keep the formulas updated when the new month starts?
Ex. This month’s sheet name is Commission – September
Next months’ is Commission – October
But we use the same prelim sheet and just copy the data from the commission sheet.
2. The first formula that we used is for the quantity of items sold; how do I copy the money value to the next column? The commission sheet format never changes.

Sample attached.

Hi,

I actually figured this one out: (2. The first formula that we used is for the quantity of items sold; how do I copy the money value to the next column? The commission sheet format never changes.)

=INDEX('[Copy of Book1 B.xlsx]Sheet1'!$C$2:$AU$7,MATCH($B$1,'[Copy of Book1 B.xlsx]Sheet1'!$C$2:$C$7,0),MATCH(A2,'[Copy of Book1 B.xlsx]Sheet1'!$C$1:$AU$1,0)+1)

Added the +1 to the formula



April

Hi Jindon,

I must be honest that I have zero VBA knowledge, so I don't even know what the above code means *I feel embarrassed*

I think that the formulas that JeteMC has given me has helped the most so far - but thank you for the effort!!!

JeteMC - I could maybe try to paste over the old data in the commission sheet and File > Save As > New name for both. Do you think that would work?

I feel really silly asking all these questions, but when I try to google these things, it's not much help.

Is there a way that I could copy the formula to all 20 tabs at the same time without changing the references?

April

This is to show how the code works as you posted the question in VBA forum.
You will see a dialogue box after you click on the button to select the file to be imported, so select "Commission Calculations Sample.xlsx".
As I mentioned in the previous post, not really sure the order of the output.

Jindon,

This was exactly what I was looking for. We have about 5 workbooks with 15-20 sheets each with about 5-10 employees on each sheet. (400 plus employees in total)

Column B is for QTY and column C is for the money value. How would I add the money value as well? And do it for more than 400 employees?

Even if each sheet has its own click button - its better than copy and pasting all the data.

April

JeteMC,

I think I'm going to use the formulas that you helped with until I can build the code to do what Jindon helped with.

You guys are truly amazing

April

Originally Posted by April88

This was exactly what I was looking for. We have about 5 workbooks with 15-20 sheets each with about 5-10 employees on each sheet. (400 plus employees in total)

Column B is for QTY and column C is for the money value. How would I add the money value as well? And do it for more than 400 employees?

I don't understand the relation between those 5 workbooks with multiple sheets and the result.
If you make up few dummy data with multiple worksheets and the result workbook, I will see if it can be done like the way I did or other way to accomplish the job.

Originally Posted by jindon

I don't understand the relation between those 5 workbooks with multiple sheets and the result.
If you make up few dummy data with multiple worksheets and the result workbook, I will see if it can be done like the way I did or other way to accomplish the job.

We are a sales company. Each workbook is a Province, each province has 5-20 stores (worksheets) and each store has 5-10 employees (the prelim sheet).

We have one commission sheet with all 400 + employees commission payouts and have to pull the data into the prelims and that little block we email to the sales people to check if they agree before we do payroll.

We used to copy paste each persons data manually to their prelim block, but this takes too much time. Hence the reason I asked the forum for help to see if we can automate it.

Hope that makes sense?

Originally Posted by April88

We are a sales company. Each workbook is a Province, each province has 5-20 stores (worksheets) and each store has 5-10 employees (the prelim sheet).

We have one commission sheet with all 400 + employees commission payouts and have to pull the data into the prelims and that little block we email to the sales people to check if they agree before we do payroll.

We used to copy paste each persons data manually to their prelim block, but this takes too much time. Hence the reason I asked the forum for help to see if we can automate it.

Hope that makes sense?

Hi Jindon,

Please see attached sample data. The workbooks must be kept separate, because we get a new commission sheet each month.

There is also a word document attached that tells you which headers in the commission sheet link to the Prelim sheet.

Thank you very much

April

Originally Posted by JeteMc

If I understand correctly you would like to put the prelim salary slip onto the same sheet as the commission table. If that is a correct understanding then yes the formulas could all have the same references.
The attached file demonstrates how this would work.

Hi JeteMc,

Sorry for the late reply, its been hectic at work.

Is there a way that we can update the formulas to reference to a new workbook for the commission sheet for next month without having to change all 500 formulas manually?

This month the workbook name is "Commission - October" & next month it will be "Commission - November".

Thanks in advance.

April

Only if the file name & the sheet name for Commission_xxx.xls are identical as you uploaded.

Please Login or Register  to view this content.