I have attached sheet as example.
I have a sales sheet that calculates commissions based on many formulas. I need these values, date and name copied and pasted to sheet 2 and then saved. I hope I have explained myself.
I have attached sheet as example.
I have a sales sheet that calculates commissions based on many formulas. I need these values, date and name copied and pasted to sheet 2 and then saved. I hope I have explained myself.
What if the base date is already next to the name in Sheet2
---
Hans
"IT" Always crosses your path!
May the (vba) code be with you... if it isn't; start debugging!
If you like my answer, Click the * below to say thank-you
anything on sheet 2 is coming from sheet1.
Sheet 2 is a muck up of what it should look like. Currently sheet 2 is empty but going forward will always be updated. Thanks for quick reply!
Do you want the comms added to the already present comms like Ron C?
doesnt matter as all i plan to do is delete mock data, and start as of today
thanks!
This is a quick and dirty one.
Just press the yellow sun on Sheet 1
comments in the VBA code explaining it a little
Just holler if you need help
Keebellah,
ALL worked great!!! I did a couple of tweaks (sort descending), etc. But all is good! I really appreciate the time you took with comments. I learned a lot just from reading the comments. NICE!
One of the things I tweaked to second sheet was add a 4th column that pulls a number from sheet one. No problem with that I simply added a ws2.Cells(xRow2, "D").Value = ws1.Cells("73", "F") into the loop. This works fine. However what I would really like is a-name b-comms - c-date - d-special number, which this works fine. But I only want d-special # to occur once per a given date. For example:
Jack 2. 04/06/16 2
John 2. 04/06/16 <----------Since this cell is same date as one above "2" is not shown in d column.
sara 4. 04/06/16 <----------Since this cell is same date as two above "2" is not shown in d column.
Would like to insert this right after te sort routine. Also are you familiar with saving as a pdf file w/o user interaction? Would like to have it set so that if cell in sheet1 contains "x" to name as blahDATETIME-FINAL.pdf. Otherwise when code is run it saves as blahDATETIME.pdf - of course you have to run code/macro.
Thank you so much for your assistance. I see you are from Netherlands....Whats weather like there? Here on the east coast (Outerbanks, NC) It cant decide between spring and winter. Yesterday it was in low 40s 35 mph winds = COLD! And today low 70s and light winds. Thanks again my friend!
Maybe something like a countif=1 then display otherwise leave blank??????
Well, glad part one worked.
You will have to add the 4th column and also modify the sort macro to include that new column when sorting
I removed the double dates and about the sorting.
If you want to keep it right you need two sort keys:
Date column Ascending and Comms column descending.
I did that now and that works, but if you run the same macro a second time without deleting the contents of Sheet2 table first then the sorting goes way off because of the empty dates.
The PDf creation has been added, I'm also attaching the resulting pdf file with name BLAH-20160407-2238.pdf, I always format my dates yyyymmdd-hhmm in this case adding hour and minute
You will have to look at the vba code and replace the contents of the variable xName with whatever you have in x in Sheet1
If xName is not blank and the Sheet2 got new values, only then the PDF file will be created and is now saved in the same folder as where this file resides.
If you want another location, you either need to add a folder selector or change the contents of the variable pdfFilePath in the CreatePDFfile Function
The weather in the Netherlands is also way off, we had higher temperatures and more sun in January and February than we had last Summer and now its getting cold and rainy again.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks