March 2017

In Microsoft, doing calculations and settling regular records can be done by formulas in the Excel office software. Sending values of a cell in Excel to target can be performed by column value copy and paste value to the target cell. In a column A if the value is zero, and in column K the value is greater than zero, copy the value of column "A" and paste it in column "K". The column value is controlled by simple copy paste functions in Windows. To add multiple columns, make the formula as "C:K". The calculation is done for the next column until the last column is reached and the value gets copied and pasted to the targeted column.

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 week's values in another cell array so that I may have a running total for the month.

Example:

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', pasted to the target. The 'skip blanks' does nothing. The macro:

Sub Macro1()

'

' Macro1 Macro

'

'

Range("C3").Select

ActiveWindow.ScrollRow = 10

Range("C3:I52").Select

Selection.Copy

Range("K3").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

True, Transpose:=False

Range("K3").Select

Application.CutCopyMode = False

End Sub

It overwrites the previous weeks' values with zeros whether I choose skip blanks or not (probably because the cells are zeros, not blank!)

I am working with the array A3:I54 and wish to copy and paste any values greater than '0' to K3:Q54 retaining any values that were previously posted in K3:Q54 that are greater than '0'.

This will compare column A with what is currently in column K........

A1 compares the value in K1

A2 compares the value in K2

etc....

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.

Sub Macro1() Dim j j = 3 Do Until Range("A" & j) = "" If Range("A" & j) = 0 Then Range("K" & j) = Range("K" & j) Else Range("K" & j) = Range("A" & j) End If j = j + 1 Loop End Sub

To add multiple columns, make the macro look like "C:K"

Sub Macro1() Dim j j = 3 Do Until Range("C" & j) = "" If Range("C" & j) = 0 Then Range("K" & j) = Range("K" & j) Else Range("K" & j) = Range("C" & j) End If If Range("D" & j) = 0 Then Range("L" & j) = Range("L" & j) Else Range("L" & j) = Range("D" & j) End If If Range("E" & j) = 0 Then Range("M" & j) = Range("M" & j) Else Range("M" & j) = Range("E" & j) End If If Range("F" & j) = 0 Then Range("N" & j) = Range("N" & j) Else Range("N" & j) = Range("F" & j) End If If Range("G" & j) = 0 Then Range("O" & j) = Range("O" & j) Else Range("O" & j) = Range("G" & j) End If If Range("H" & j) = 0 Then Range("P" & j) = Range("P" & j) Else Range("P" & j) = Range("H" & j) End If If Range("I" & j) = 0 Then Range("Q" & j) = Range("Q" & j) Else Range("Q" & j) = Range("I" & j) End If j = j + 1 Loop End Sub

Thanks to WutUp WutUp for this tip on the forum.

Published by aakai1056.