Previous Topic Next Topic

Send value of cell to target

Solved/Closed
Paul - Mar 19, 2009 at 12:15 PM
 Paul - Mar 26, 2009 at 11:01 AM
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.
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', paste 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


over-writes 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'

Any thoughts? Thanks in advance!! ..Paul

7 responses

Answer 1 / 7
WutUp WutUp
Mar 19, 2009 at 08:56 PM
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
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
0
Answer 2 / 7
Paul
Mar 20, 2009 at 02:30 PM
WutUp WutUp... That's PERFECT!!! You're a wizard!! I can't tell you how long I've tried to do this myself!

Now, how can I add multiple columns ie: C through I copied to K through Q? The macro doesn't like "C:K"

Thanks in advance ... Paul
0
WutUp WutUp
Mar 20, 2009 at 07:23 PM
See if this works.

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
0
Answer 3 / 7
Paul
Mar 20, 2009 at 11:26 PM
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
0
WutUp WutUp
Mar 21, 2009 at 11:50 AM
In the code of line below, where I have 'Check name of sheet, if your sheet names are capitalized then change it. So, "emp*" would be "Emp*".


Sub RunPayroll()

Dim EmpCount As Integer
Dim I As Integer
EmpCount = ActiveWorkbook.Worksheets.Count

For I = 1 To EmpCount

If Sheets(I).Name Like "emp*" Then 'Check name of sheet
Sheets(I).Activate
Call Macro1
End If

Next I

End Sub
0
Paul > WutUp WutUp
Mar 22, 2009 at 04:06 PM
Hi WutupX2
My sheets do have a capitol 'E' for the first letter of employee. When I run the macro as a separate module, it can't find 'Macro1'. When it is combined, the line 'If Range("C" & j) = 0 Then' stops it as an error. Thanks a heap for all of you expertise! Here's the code:


Sub RunPayroll()

Dim EmpCount As Integer
Dim I As Integer
EmpCount = ActiveWorkbook.Worksheets.Count

For I = 1 To EmpCount

If Sheets(I).Name Like "Emp*" Then 'Check name of sheet
Sheets(I).Activate
Call Macro1
End If

Next I

End Sub
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
0
WutUp WutUp > Paul
Mar 22, 2009 at 04:35 PM
It should not matter if it is in a different module. That is how I ran the code. Anyway, if you put it in the same module then you should have a line separator between End Sub of the RunPayroll and Macro1. In your data, are there any empty cells or non-numeric values?
0
Paul > WutUp WutUp
Mar 23, 2009 at 11:44 AM
No, there are no empty or non-numeric values in the cells. The code in message 3 above works great on each individual sheet. I assigned a keystroke to run the code when I bring up each worksheet that looks like the code below. Now, I view the worksheet, press ctrl+j to run the macro, and it posts the data perfectly skipping values of '0' and leaving previously posted data alone. In many ways, this is better since it allows me to confirm the data visually before it posts to the summary. Still, it would be nice to run the code just once to post data to all of the worksheets.

A line between the 2 codes automatically appears. Is that enough? I don't know how to manually insert a line. Did I mention that I'm using Office 97? Maybe that makes a difference. When making 2 separate modules for the code, the error message says that it can not find Macro1 even though the 2nd module is clearly renamed Macro1.

Again, your help has been invaluable. I hope that others may benefit from our posts the way that I have.

Here's the present code that works with a keystroke on each individual sheet:

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+j
'
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
0
WutUp WutUp > Paul
Mar 23, 2009 at 01:52 PM
Can you provide an example file? I have run the macro with excel 2000 and 2007 without a problem.
0
Answer 4 / 7
Paul
Mar 24, 2009 at 11:09 AM
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!

Here's the address of the file:
http://page453.110mb.com/payroll19temp.xls
My French is not up to speed so I'm afraid you'll have to copy and paste the link instead of click on it as a hyperlink.

Thanks for all of your help!! ..Paul
0
WutUp WutUp
Mar 24, 2009 at 05:57 PM
OK, I had it running on all sheets. I also have it where you can click on cell H2 in Payroll Calculator as you requested..
There is one problem though with the error you were encountering. Each sheet for Employee1, 2, and 7 have an error on line 14. I looked at the formulas, and did not see anything wrong, so maybe it is the cell formatting.
Anyway, you can try it and see if it works the way you want it to. Let me know if you need further assistance.

http://www.4shared.com/file/94789962/5d02749b/payroll19temp.html
0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 7
WutUp WutUp
Mar 24, 2009 at 09:45 PM
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.

http://www.4shared.com/file/94819494/c7678e96/payroll19temp.html
0
Answer 6 / 7
Paul
Mar 25, 2009 at 01:12 PM
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".

Heres the latest file:
http://page453.110mb.com/payroll19tempc.xls
0
Answer 7 / 7
WutUp WutUp
Mar 25, 2009 at 04:19 PM
Ok, a message box will "pop up" to verify. If no is clicked, the macro exits and cell M2 is selected. If yes is clicked,
the macro will update all sheets.


http://www.4shared.com/file/94972137/31575449/payroll19tempc.html
0
Paul
Mar 25, 2009 at 07:31 PM
Watup Watup,
This is a perfect, completed project that I realize now, I could never have done without your help. How could I ever repay you? Your generosity of experience and time is truly a valuable asset to this forum. Thank you, my friend for your patience and guidance through, what turned out to be, a big programming job! ...Paul
0
Paul > Paul
Mar 26, 2009 at 11:01 AM
Conclusions, summary and code:

This summary assumes that at least, the worksheets named "Payroll Calculator" and

"Employee1" have been created. More Employee worksheets may be added by changing the numeric

value to 2, 3, 4 etc. Adjust names and applications to suit.

1. Open Excel>press alt+f11>click on modules>click insert>module>copy and paste the code

here for macro1. Save. This will copy numeric values greater than zero on the active

worksheet column C through column I and paste it to the same worksheet column K through

column Q without disturbing any previously posted values in K through Q. The keystroke

ctrl+j may be used to execute this macro. Adjust column letters as necessary.

2. Click on modules>click insert>module>copy and paste the code here for RunPayroll. Save.

This macro will call macro1 to execute on all worksheets beginning with the letters emp and

starting with the worksheet named Employee1. Adjust the worksheet names "Employee1", "Emp*"

and "Payroll Calculator" to suit. This macro ends by activating the cell D3 on the worksheet

named "Payroll Calculator".

3. Click on Payroll Calculator>click insert>module>copy and paste the code here for

Worksheet_SelectionChange. Save. This macro enables the user to call and run the RunPayroll

macro by clicking the cell H2 on the worksheet named "Payroll Calculator". It also invokes a

confirmation requiring user input to proceed. Adjust names and cell locations to suit. The

cell H2 may only be modified using the "design mode". (View>toolbars>control toolbox>design

mode).

Here are the 3 codes. For clarification, the codes are separated by a line. Do not copy the

line.

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+j
'
Dim j
j = 3

On Error Resume Next

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

________________________________________________________________

Sub RunPayroll()

Dim EmpCount As Integer
Dim I As Integer
EmpCount = ActiveWorkbook.Worksheets.Count

Sheets("Employee1").Activate

For I = 3 To EmpCount

If Sheets(I).Name Like "Emp*" Then
Sheets(I).Activate
Call Macro1

End If

Next I

Sheets("Payroll Calculator").Activate
Range("D3").Select

End Sub

__________________________________________________________________



Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim answer As String

If Target.Address = "$H$2" Then

answer = MsgBox("Please check the date in M2." & vbCrLf & _
"This action will post new data to the Employee and Summary sheets." & vbCrLf & _
"Proceed? Yes / No ", vbQuestion + vbYesNo, "Update")

If answer = vbYes Then

Call RunPayroll

Else

Range("M2").Select

End If
End If


End Sub
0

Similar discussions

How to send audio in Instagram
suyaprakash8429 -
Andy._Tani -

2 responses
Excel - IF function with multiple words
Nick -
user -

14 responses
help me to add particular cells
mathewmunna -
mathew munna -

2 responses
If cell = date return yes or no.
JD -
ndwebb -

5 responses
Facebook want to verify my ID
Hoopla1234 -
Rennosh -

9 responses