Is it possible to send the value of a cell (copy and paste special) to another cell where the value would not be disturbed when the source value changes to '0'?
My payroll calculator summary worksheet gets information from other worksheets each week within the Excel file and calculates the current week's payroll. The previous week had values, but now, during the current week, the cells for the previous week are 0.
My goal is to retain the previous weeks values in another cell array so that I may have a running total for the month.
A3 has the formula:
=IF(A3='Payroll Calculator'!M2,VLOOKUP(H1,'Payroll Calculator'!$B$3:$M$28,8,FALSE),0)
That value needs to be posted to K3 but only if more than '0'.
If a3 returns a value of '0' then any value greater than '0' that was previously posted in k3 would be left alone.
I have been easily doing this manually by simply copying the values that I want to save, then using the 'paste special' command and choosing 'values', paste to the target. The 'skip blanks' does nothing. The macro:
Maybe this can get you in the right direction if my interpretation is correct.
This will compare column A with what is currently in column K........
A1 compares the value in K1
A2 compares the value in K2
If column A is zero and column K is greater than zero, then keep the value in column K. Otherwise copy the
value in column A to column K.
OK.. That's works great!... just add more code addressing each cell. I thought you could do an array as you would a formula in a cell.
The company I work for has very specific and special payroll needs so I wrote this payroll for them to handle up to 25 employees. Each has his own summary sheet requiring this macro to be run. Can I run the macro once by adding the code "activate employee1" "activate employee2" etc then copy and paste the macro code? The worksheet names are just that: "employee1" through "employee25"
You can not know how much I appreciate your help in macros!! Many thanks again! ...Paul
No problem! I've enclosed a link to the entire program. I've removed sensitive information and changed names etc. to protect people's privacy. This version posted payroll for the week ending Mar 21. I have not run the macro to post to the summary. Ctrl+j will do this on each worksheet named "Employee*".
Change the numbers in "Employee*" C:I by changing the information on "Payroll Calculator".
Change the date on "Payroll Calculator" to post info on new rows of "Employee*"
The macro posts the data to "Employee*" K:Q and, in turn, calculates quarterly totals and posts them to "Summary" for use in quarterly reports.
What would be nice is to click the cell "Payroll Calculator H2" to run the macro that posts all the data to all of the Employee worksheets at once. At least, this was my original goal!
OK, try this version instead. I saw the error was coming with the sheet named "Employee Information."
So, since the coding was saying "Like Emp*", it was selecting that sheet. I changed the macro to start
counting at sheet three and beyond so it will skip that sheet. I think it is ok now. Just a word of caution.
If you want to type anything in cell H2, make sure you put the sheet in design mode first or the macro will
run wheather you click on the cell or tab into it.
I didn't have the trouble with line 14 that you experienced on the previous version. Maybe it's an Office 97 thing. Your new one works great, though. You're amazing!
Now I see that it is possible that a user could accidentally run the macro by clicking on the H2 cell without changing the date thereby over-writing the previous week's values and upsetting the total file with wrong numbers. Is it possible to call a dialogue box that pops up when you click on H2 before the macro actually runs? The box could say "Please check the date in M2. This action will post new data to the Employee and Summary sheets. Proceed? Y=yes N=no".