Search : in
By :

Send value of cell to target

Last answer on Mar 26, 2009 3:01:23 pm GMT Paul, on Mar 19, 2009 4:15:16 pm GMT 
 Report this message to moderators

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

Configuration: Windows XP
Firefox 3.0.7

Best answers for « Send value of cell to target » in :
Excel - Send value of cell to target Show Excel - Send value of cell to target Issue Solution Note Issue 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...
Checking multiple Cell values and validating Show
Custom CELL Address to Field Value Show
Excel tips : How to insert date in a cell ShowExcel tips : How to insert date in a cell Below are some tips on how to insert date and time in an excel cell for a specific purpose:- To insert current date, press CTRL + ; in the chosen cell. To insert current time, press CTRL+...
[VBA] Detecting changes in cell Show[VBA] Detecting changes in cell The Event Change feature of a sheet will detects the change in the active cell but it gives no information about the content. The example given below will help you to find out if the cell was changed,...
[VBA] Deleting a word in a range of cell Show[VBA] Deleting a word in a range of cell In the case you want to delete a word in a sentence, just create a small macro that removes the word. But it will become difficult when you have word like, for example, "Theword" or "THEWORD" or...
Download USA Unlisted Cell Phone Numbers ShowHas you friend flown for America without telling you where he/she would settle? Dont panic! USA Unlisted Cell Phone Numbers is a program which can help you. By using this program, you simply need to know his/her phone number in order to find...
Download Business Valuation Model Excel Show
Download Internet Cell Boost Show
Worksheet - Cells ShowThe Concept of a Cell A "cell" is the intersection between a line (horizontal) and a column (vertical) on a worksheet. Thus, the name of the line combined with the name of the column gives the cell's coordinates (the term address is sometimes also...
Spreadsheets - Cell Selection ShowCell Selection Spreadsheets are powerful tools for working with data. However, to work with data, it is necessary to have tools to rapidly choose the required cells. Line Selection An entire line can be chosen by clicking directly on the line...
Ask your question Reply

1

WutUp WutUp, on Mar 20, 2009 12:56:46 am GMT

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

   
Reply to WutUp WutUp

2

Paul, on Mar 20, 2009 6:30:28 pm GMT

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

   
Reply to Paul

3

WutUp WutUp, on Mar 20, 2009 11:23:09 pm GMT

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

   
Reply to WutUp WutUp

4

Paul, on Mar 21, 2009 3:26:49 am GMT

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

   
Reply to Paul

5

WutUp WutUp, on Mar 21, 2009 3:50:17 pm GMT

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

   
Reply to WutUp WutUp

6

Paul, on Mar 22, 2009 8:06:14 pm GMT

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

   
Reply to Paul

7

WutUp WutUp, on Mar 22, 2009 8:35:31 pm GMT

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?

   
Reply to WutUp WutUp

8

Paul, on Mar 23, 2009 3:44:27 pm GMT

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

   
Reply to Paul

9

WutUp WutUp, on Mar 23, 2009 5:52:12 pm GMT

Can you provide an example file? I have run the macro with excel 2000 and 2007 without a problem.

   
Reply to WutUp WutUp

10

Paul, on Mar 24, 2009 3:09:37 pm GMT

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

   
Reply to Paul

11

WutUp WutUp, on Mar 24, 2009 9:57:59 pm GMT

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

   
Reply to WutUp WutUp

12

WutUp WutUp, on Mar 25, 2009 1:45:26 am GMT

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

   
Reply to WutUp WutUp

13

Paul, on Mar 25, 2009 5:12:12 pm GMT

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

   
Reply to Paul

14

WutUp WutUp, on Mar 25, 2009 8:19:48 pm GMT

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

   
Reply to WutUp WutUp

15

Paul, on Mar 25, 2009 11:31:21 pm GMT

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

   
Reply to Paul

16

 Paul, on Mar 26, 2009 3:01:23 pm GMT

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

   
Reply to Paul
Ask your question Reply
They need your help