PDA

View Full Version : VBA Code to Compare a number of different worksheets and change/highlight differences



snowfroguk
10-14-2015, 08:44 AM
Hi,

I need a relatively complicated (for me!) VBA code to do the following:

We have a master spreadsheet which has all of our current employees and their salary and grade in each month. I need to clarify each quarter that their salary and grade for each month is the same, and pull through the amended salary / grade if it is not.

Unfortunately this means that I have to check the salary / grade columns for each month against a spreadsheet for that month (going back to the beginning of the year in every instance, in case of backdated changes).

I have written the VBA which pulls through all of the current data for the months to different spreadsheets in each tab and saves the workbook, but I now need to automatically:

- Pull through new employees who were not already in the spreadsheet (so any employee who appears in any of the individual month worksheets but not in the master)
- Check salaries and grades for each month column against the relevant month worksheet and overwrite if different
- Highlight in a different colour any changes that have been made in order to spot check grade changes for legacy rights
- Set salary / grade to 'N/A' in months where the employees hadn't started (i.e. the vlookup throws up an #N/A)

I hope that makes sense, I've tried doing bits and pieces, but I'm fairly new to VBA, and my brain is turning to mush!

Thanks very much

SamT
10-14-2015, 11:01 AM
Your explanation has insufficient detail.

Prepare a workbook for sharing

In each name column Use Ctrl+H to change each of the vowels in the Column to a Randomly picked consonant.

Randomly choose some different consonants and again change all instances of those consonant to a different one. The actual values don't matter to us, You can even use some numbers, as long as they all have mostly letters.

In all salary columns, (and any Employee ID columns), that are not formulas, Randomly change a few numbers. The actual values don't matter to us, as long as they are all numbers so they don't crash any formulas.

Delete all but 10 or 20 of the top rows, to shrink the size of the workbook. Only include one monthly sheet, All we need to see is the layout and Number Formats. Sure, you are on a company leased T1 line, but we are all over the world and some of use pay by used bandwidth or have slow connections.

Important!: Remove all Conditional Formatting.

You get the idea.

Now using the Go Advanced Option, you can open the VBA Express Advanced Editor. Below the Editor, use the Manage Attachments button to upload the sterile Workbook.

snowfroguk
10-14-2015, 11:34 AM
Ok, thanks Sam. I'll send all the relevant info tomorrow when I'm back in the office - sorry for being such a newb!

snowfroguk
10-15-2015, 02:32 AM
So I've created a sheet where the lookup for Jan has already been pulled through, and the new report for Feb has been generated but nothing has been done with it yet.

So I need to:

- Check the Jan tab against existing data for Jan to see if it is still the same for Grade and Salary - and pull through / highlight where different
- Pull through the data for Feb from the Feb tab
- Add new joiners that don't yet appear in the report

Hope this is sufficient, please let me know if you need anything else!

SamT
10-15-2015, 08:03 AM
Here's the plan for the code. You must look at the Actual workbook and carefully apply the logic in the plan to the Actual workbook to see if there are any conflicts or inconsistencies. The best way to check the plan is on a copy of the actual workbook, manually perform steps 1 thru all of step 3 exactly as the plan states. And, Padawan, think not, just read and do..:whip



Sort all the sheets in the workbook by Column "A"
Count the number of Rows used in Sheet Master
In Month number order count the number of Rows used in every other sheet in the workbook:

If the Month sheet has more rows
Compare IDs to the Mastersheet, and if missing from the Master then:
Add The first 5 columns to the Master sheet
Recount the Rows on the Master sheet and go to the Next Month sheets and repeat.


Back at the Master Sheet:
Sort the Master Sheet on IDs
For each ID in the Master sheet

For each Month in the Master Sheet
If the Salary column is not empty on that Month sheet,

On that ID in that Mon sheet:

Compare Salary, Grade, and bonus:
If different, update the Master sheet
If missing put "N/A's" in the Master sheet

snowfroguk
10-15-2015, 08:56 AM
Awesome, I like this style! I still get to flex my newly acquired VBA skills!! Here goes nothing... I'll post the code I end up with online and let you know if I have any issues!

Thanks Sam - wish me luck!!

SamT
10-15-2015, 04:23 PM
:thumb Good Luck.

What ya gonna do, What ya gonna do, What ya gonna do, When the bad boy gets fired and ain't onna list no more?