one code to do multiple things

hi, i have the code below which takes the value in cell U17 everytime it 
changes, and places it in column A on a sheet called graphs thereby forming a 
list down the sheet of all the values that appear in cell U17, for charting 
purposes.

What i would like to do is to be able to duplicate this for different cells. 
ie have the cell values of W25 appear in a list in column K on the graphs 
sheet, and so on. this code was written for me by someone else as, could you 
please give me some code that will enable me to capture the values for two or 
three cells in sheet1 into the sheet called graphs?

any help would be greatly appreciated, thank you.

Private Sub Worksheet_Calculate()
    Dim Dest As Range
    With Sheets("graphs")
        Set Dest = .Range("A" & Rows.Count).End(xlUp)
        If Range("U17") <> Dest Then _
            Dest.Offset(1) = Range("U17")
    End With
End Sub

-- 
thanks
0
Utf
2/24/2010 7:09:01 AM
excel.newusers 15348 articles. 1 followers. Follow

4 Replies
489 Views

Similar Articles

[PageSpeed] 57

Hi try this, it works on the activecell, if it has a formula then the
value is transferred.


VBA Code:
--------------------
  

  
Private Sub Worksheet_Calculate()
Dim Dest As Range
With Sheets("graphs")
Set Dest = .Range("A" & Rows.Count).End(xlUp)
If ActiveCell.HasFormula And ActiveCell <> Dest Then _
Dest.Offset(1) = ActiveCell
End With
End Sub
--------------------



 
 
 


Morgan;652633 Wrote: 
> 
hi, i have the code below which takes the value in cell U17 everytime
it
changes, and places it in column A on a sheet called graphs thereby
forming a
list down the sheet of all the values that appear in cell U17, for
charting
purposes.
 
What i would like to do is to be able to duplicate this for different
cells.
ie have the cell values of W25 appear in a list in column K on the
graphs
sheet, and so on. this code was written for me by someone else as,
could you
please give me some code that will enable me to capture the values for
two or
three cells in sheet1 into the sheet called graphs?
 
any help would be greatly appreciated, thank you.
 
Private Sub Worksheet_Calculate()
Dim Dest As Range
With Sheets("graphs")
Set Dest = .Range("A" & Rows.Count).End(xlUp)
If Range("U17") <> Dest Then _
Dest.Offset(1) = Range("U17")
End With
End Sub
 
--
thanks


-- 
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: 1
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=182101

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]

0
Simon
2/24/2010 12:13:59 PM
hi i tried it but i couldn't get it to work
-- 
thanks


"Simon Lloyd" wrote:

> 
> Hi try this, it works on the activecell, if it has a formula then the
> value is transferred.
> 
> 

> VBA Code:
> --------------------
>   
> 
  
> Private Sub Worksheet_Calculate()
> Dim Dest As Range
> With Sheets("graphs")
> Set Dest = .Range("A" & Rows.Count).End(xlUp)
> If ActiveCell.HasFormula And ActiveCell <> Dest Then _
> Dest.Offset(1) = ActiveCell
> End With
> End Sub

> --------------------
> 
> 
> 
>  
>  
>  
> 
> 
Morgan;652633 Wrote: 
> > 
> hi, i have the code below which takes the value in cell U17 everytime
> it
> changes, and places it in column A on a sheet called graphs thereby
> forming a
> list down the sheet of all the values that appear in cell U17, for
> charting
> purposes.
>  
> What i would like to do is to be able to duplicate this for different
> cells.
> ie have the cell values of W25 appear in a list in column K on the
> graphs
> sheet, and so on. this code was written for me by someone else as,
> could you
> please give me some code that will enable me to capture the values for
> two or
> three cells in sheet1 into the sheet called graphs?
>  
> any help would be greatly appreciated, thank you.
>  
> Private Sub Worksheet_Calculate()
> Dim Dest As Range
> With Sheets("graphs")
> Set Dest = .Range("A" & Rows.Count).End(xlUp)
> If Range("U17") <> Dest Then _
> Dest.Offset(1) = Range("U17")
> End With
> End Sub
>  
> --
> thanks
> 
> 
> -- 
> Simon Lloyd
> 
> Regards,
> Simon Lloyd
> 'Microsoft Office Help' (http://www.thecodecage.com)
> ------------------------------------------------------------------------
> Simon Lloyd's Profile: 1
> View this thread: http://www.thecodecage.com/forumz/showthread.php?t=182101
> 
> [url="http://www.thecodecage.com"]Microsoft Office Help[/url]
> 
> .
> 
0
Utf
2/24/2010 8:58:01 PM
      If desired, send your file to my address below. I will only look if:
      1. You send a copy of this message on an inserted sheet
      2. You give me the newsgroup and the subject line
      3. You send a clear explanation of what you want
      4. You send before/after examples and expected results.


-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Morgan" <Morgan@discussions.microsoft.com> wrote in message 
news:074FDBC7-F04B-4E36-96B0-9E54165AF37B@microsoft.com...
> hi i tried it but i couldn't get it to work
> -- 
> thanks
>
>
> "Simon Lloyd" wrote:
>
>>
>> Hi try this, it works on the activecell, if it has a formula then the
>> value is transferred.
>>
>>
>
>> VBA Code:
>> --------------------
>>
>>
>
>> Private Sub Worksheet_Calculate()
>> Dim Dest As Range
>> With Sheets("graphs")
>> Set Dest = .Range("A" & Rows.Count).End(xlUp)
>> If ActiveCell.HasFormula And ActiveCell <> Dest Then _
>> Dest.Offset(1) = ActiveCell
>> End With
>> End Sub
>
>> --------------------
>>
>>
>>
>>
>>
>>
>>
>>
> Morgan;652633 Wrote:
>> >
>> hi, i have the code below which takes the value in cell U17 everytime
>> it
>> changes, and places it in column A on a sheet called graphs thereby
>> forming a
>> list down the sheet of all the values that appear in cell U17, for
>> charting
>> purposes.
>>
>> What i would like to do is to be able to duplicate this for different
>> cells.
>> ie have the cell values of W25 appear in a list in column K on the
>> graphs
>> sheet, and so on. this code was written for me by someone else as,
>> could you
>> please give me some code that will enable me to capture the values for
>> two or
>> three cells in sheet1 into the sheet called graphs?
>>
>> any help would be greatly appreciated, thank you.
>>
>> Private Sub Worksheet_Calculate()
>> Dim Dest As Range
>> With Sheets("graphs")
>> Set Dest = .Range("A" & Rows.Count).End(xlUp)
>> If Range("U17") <> Dest Then _
>> Dest.Offset(1) = Range("U17")
>> End With
>> End Sub
>>
>> --
>> thanks
>>
>>
>> -- 
>> Simon Lloyd
>>
>> Regards,
>> Simon Lloyd
>> 'Microsoft Office Help' (http://www.thecodecage.com)
>> ------------------------------------------------------------------------
>> Simon Lloyd's Profile: 1
>> View this thread: 
>> http://www.thecodecage.com/forumz/showthread.php?t=182101
>>
>> [url="http://www.thecodecage.com"]Microsoft Office Help[/url]
>>
>> .
>> 

0
Don
2/25/2010 1:06:16 PM
Morgan,
You have asked this question at least 4 different times over the past few 
days and have received several responses.  Apparently none has provided a 
solution?

It would help us all to help you if you can stick to a single discussion 
thread so that we can see all that has been tried and find out why the 
offered solutions have failed.

My first question THIS time, is why are you still trying to use the 
_Calculate() event rather than the _Change() event?

"Morgan" wrote:

> hi i tried it but i couldn't get it to work
> -- 
> thanks
> 
> 
> "Simon Lloyd" wrote:
> 
> > 
> > Hi try this, it works on the activecell, if it has a formula then the
> > value is transferred.
> > 
> > 
> 
> > VBA Code:
> > --------------------
> >   
> > 
>   
> > Private Sub Worksheet_Calculate()
> > Dim Dest As Range
> > With Sheets("graphs")
> > Set Dest = .Range("A" & Rows.Count).End(xlUp)
> > If ActiveCell.HasFormula And ActiveCell <> Dest Then _
> > Dest.Offset(1) = ActiveCell
> > End With
> > End Sub
> 
> > --------------------
> > 
> > 
> > 
> >  
> >  
> >  
> > 
> > 
> Morgan;652633 Wrote: 
> > > 
> > hi, i have the code below which takes the value in cell U17 everytime
> > it
> > changes, and places it in column A on a sheet called graphs thereby
> > forming a
> > list down the sheet of all the values that appear in cell U17, for
> > charting
> > purposes.
> >  
> > What i would like to do is to be able to duplicate this for different
> > cells.
> > ie have the cell values of W25 appear in a list in column K on the
> > graphs
> > sheet, and so on. this code was written for me by someone else as,
> > could you
> > please give me some code that will enable me to capture the values for
> > two or
> > three cells in sheet1 into the sheet called graphs?
> >  
> > any help would be greatly appreciated, thank you.
> >  
> > Private Sub Worksheet_Calculate()
> > Dim Dest As Range
> > With Sheets("graphs")
> > Set Dest = .Range("A" & Rows.Count).End(xlUp)
> > If Range("U17") <> Dest Then _
> > Dest.Offset(1) = Range("U17")
> > End With
> > End Sub
> >  
> > --
> > thanks
> > 
> > 
> > -- 
> > Simon Lloyd
> > 
> > Regards,
> > Simon Lloyd
> > 'Microsoft Office Help' (http://www.thecodecage.com)
> > ------------------------------------------------------------------------
> > Simon Lloyd's Profile: 1
> > View this thread: http://www.thecodecage.com/forumz/showthread.php?t=182101
> > 
> > [url="http://www.thecodecage.com"]Microsoft Office Help[/url]
> > 
> > .
> > 
0
Utf
2/25/2010 4:31:02 PM
Reply:

Similar Artilces:

can excel check one column against another
Hi. I have a spreadsheet with columns such as A, B, C, D, filled with numbers. Each row across such as row 1, then row 2, then row 3, etc of numbers is totaled in column E. Countless rows, each of which is totaled per row and then ends with a grand total at bottom of column E. Next comes columns F, G, H, also filled with numbers (but different numbers), which is totaled per row in Column I. At the bottom of Column I is a grand total. Even though arrived at using different sets of numbers, -both grand totals - in columns E and I must match. Sometimes they don't match. Ouch. So here is m...

why crash in the second method of one interface
Hi, Using one class generated from *.tlb to call 2 methods of one COM. The strange thing is that I got one error of the "First-chance exception in : 0xC0000005: Access Violation" in the second method. I also use ASP web page to call both methods of this COM and both methods seems is OK. ITemplateManage mytemplate; if (!mytemplate.CreateDispatch("comobj.TemplateManage")) { IDE_Error::Report (ComError|1,"ComObj.TemplateManage in FUN_Template::Add()"); return false; } result = mytemplate.Check (templatefilename); decodeparameter=mytemplate.GetInfo (templ...

MSR Credit Card Reader adds all the information on one line
Is there a way to get the CC reader to properly seperate the the acct # from the expiration date? When I swipe I currently get everything on the acct # line. Thanks in advance! What type of CC reader(MSR) to you have? Typically this is an issue with simply configuring the MSR. Rob "Steakum" wrote: > Is there a way to get the CC reader to properly seperate the the acct # from > the expiration date? When I swipe I currently get everything on the acct # > line. > > Thanks in advance! Its a IDTECH Minimag USB/Keyboard Interface Do I need to select the Wedge Key...

Duplicate Transactions, One When Pending, One When Posted
I'm sure this has been addressed, but I couldn't seem to find an answer searching: My problem is, a transaction occurs at my bank, and MM2K7 downloads the data for it. Once that transaction moves from "Pending" to "Posted" at my bank, the transaction data changes, so MM downloads it again. Sometimes I get a possible duplicate message from MM, sometimes I don't. What are people doing to handle this situation? Thank you. This is a problem with your bank, not with Money. Eventually they should fix it. Call them. In the meantime VOID the pending transacti...

grid off on one page
Is it possible to turn the grid off of only one page? thanks On Thu, 14 Feb 2008 10:37:11 -0600, "greg" <iuouh@ghfhg.com> wrote: >Is it possible to turn the grid off of only one page? Not really as it is a document wide setting.... however, there is workaround open the page shapesheet menu window -> Show shapesheet and set XGridSpacing and YGridSpacing to a large value. -- Regards, Paul Herber, Sandrila Ltd. SSADM for Visio http://www.saadm.sandrila.co.uk/ On Thu, 14 Feb 2008 16:47:53 +0000, Paul Herber <SubstituteMyFirstNameHere@pherber.com> wro...

Ranking Multiple Columns
Here is an example of a report I run every month, What I do is once I get the Data Listed Below, I add in the 1-5, 6-12, & the 13+ columns, Then I Sort the Data by RESPAR then by DAYS, then I manually Count the Days 1-5 to get a Count of Days between 0 & 5 days, then the same for 6-12 days & 13+ days. I do this by putting a 1 in the First record row with a 0 then hold down the Ctrl KEy and dragging till I reach a day of 6, then start that column over, going to 13. I have about 2000 or more records every month. Is there a formula I can use that will do this for me, with out ...

One to One relationship
I have an Events entity that is an offshoot from the Contacts entity. Basically, it tracks what events each contact has been invited to, and is used mainly by the Events Coordinator. I've set it up as a One (Contact) to Many (Events) relationship. However, I really only want one event form for each contact form. The reason I've set up a different entity rather than just adding a tab is that access to this entity needs to be tightly controlled. Obviously, if many events forms are created for the one contact, it would be very difficult to tell which one is accurate etc. Is it possibl...

Next Date given one date
At this time, I cannot use VBA. Here is my objective: For a given treasury note security: Cell A1, Date 6/17/2004 - today's date Cell A2, Date 7/15/1997 - first coupon date I want to identify the "previous coupon date" and "next coupon date" from today's date based on the frequency of the "first coupon date". Logically speaking: * Security pays 2 times each year * Based on first coupon date, pay dates are: 1/15 & 7/15 * Given today is 6/17/04, the next pay date is 7/15/04. * Given today is 6/17/04, the previous pay date is 1/15/04. Can this be...

route inbound email from ip to one folder
Is there a way to route any incoming email from a certain IP address to a single folder? Our company is receiving emails from a source that we do not wish to receive, and would like these email to be directed to a singe folder rather than blocking them. Any ideas? Thanks. you could send them to the turf dir... http://support.microsoft.com/default.aspx?scid=kb;en-us;245465 "daine" <anonymous@discussions.microsoft.com> wrote in message news:11d501c4263d$f4d05110$a101280a@phx.gbl... > Is there a way to route any incoming email from a certain > IP address to a single ...

Opening Graph Format Axis Window with Vb code
dear friends, i am using ms office 2003. i have displayed a Microsoft Graph Chart in a form. in that graph if we double click at Origin( where x any y axes intersect each ohter), we will get a data table view. if we again double click at Origin, then we will get a window called Format Axis. it consits of tabs Patterns, scale,Font,Number and Alignment how do we open that window with Vb code. when i click a button that window should be opened ...

One client (Server 2k8-R2/HyperV) will not sync with DPM
We have been running DPM successfully for about a year now without any major problems. Recently we added a new server - a fairly robust PowerEdge that will be housing 4 HyperV VMs. For whatever reason, I can't get anything but the HyperV "Initial Store" to sync correctly. I thought initially it might be a problem with the HyperV machines, but it also fails to back up a small directory of files on the host drive. The oddest part about this is I really don't get an error. It will try an initial sync and run for a day or two and show very minimal data tr...

To view, specifically Units in one report
I have report with a Reporting Tree (9 Unit), when the report is generated, I select only two unit, but I need that the drill down show one report with these unit only If you want multiple units in a single report you will need to use the column layout with unit restrictions on multiple columns Leslie "Pedro" wrote: > I have report with a Reporting Tree (9 Unit), when the report is generated, I > select only two unit, but I need that the drill down show one report with > these unit only Leslie; Each Column layout have the unit restrictions Thanks :-) "Lesl...

mporting multiple text files into Excel with corresponding filenames
Dear members, I need to import 100+ text files into Excel. In addition I want Excel to show corresponding filename (where the data comes from) in each row. How do I accomplish this? TIA Hi Take a look at Ron de Bruin's site. He has lots of code examples to consolidate files. http://www.rondebruin.nl/tips.htm -- Regards Roger Govier "avi" <avi38521@gmail.com> wrote in message news:9194d7dc-21bb-4fd9-8ecb-aa301a54f7d1@b2g2000yqi.googlegroups.com... > Dear members, > > I need to import 100+ text files into Excel. > > In addition ...

Exchange 5.5 - One Organization
Hello - I am having a problem that I would like some more information on. I have One Organization (5.5) and 16 Sites (5.5) and would like to know why I can't stop the other sites from sending mail through me. If I can, I would like to know how. Thanks for any help. what do you mean by "sending mail through me"? What mail? Internet email? Does each site have its own Internet Mail Service? Or is your site kind of the "hub" where all Internet email comes in and goes out? -- Susan Conkey [MVP] "dave.mudgett" <davemudgett@discussions.microsoft....

See more than one tab at a time
I would like to be able to see more than one Tab at a time in Excel. Currently I'm able to see two Tabs from different worksheets, but not two or more tabs from the same worksheet On Sat, 28 Aug 2004 14:57:03 -0700, "rocky59" <rocky59@discussions.microsoft.com> wrote: >I would like to be able to see more than one Tab at a time in Excel. >Currently I'm able to see two Tabs from different worksheets, but not two or >more tabs from the same worksheet That would be because each tab represents a single worksheet. Clicking on the tab would move you from where ...

Why Would You Lose Sound On One User
WXP Pro Version 2002 Service Pack 3 with all current updates Dell Pentium(R) D CPU 2.80Ghz 2.79Ghz, 512 MB RAM System has three users all with admin rights - User 1(primary - me), User 2 (wife), and System Admin. (which I made visible on desktop). I (User 1) have lost sound when attempting to play CD's, and other audio files using WMP 11, however, I can still hear system sounds. User 2 and System Admin. still have full sound. System restore does not help ( in this case I only had a restore point from 5 days ago. Any thoughts? -- Skeeter "Old age and ...

Combining two fields into one in a query for a report
The database I am working on is to keep track of parts that are either in their storage area (on a shelf) or on an assembly line. I have two fields, one called Shelf and every part has a shelf assigned to it. The other field is Line and if a part in on an assembly line, there is a number indicating that line, if the part is on it's shelf, the word STOCK appears in the line field. (Has to be this way for the rest of the database to work, so this is not up for question) Now, I need to creat a report to show where all parts are. If they are on a line the result needs to show the l...

Tracing and tracking email on one mailbox
1. I want to use a current copy of the Exchange 2003 database and scrub it for all emails regarding one mailbox. Is there a tool that will assist me in this? 2. How do I tell Exchange 2003 to notify me of any emails transmitted to and from a particular address? And yes, this in conjunction with #1. I want to track one user's mail without using delegate or adding myself to their Active Directory profile and having to create another profile in Outlook. 1. Have a read through this: http://wm.quest.com/reg/marketing/landing/ediscoveryexchange/. This product is ideally suited for ...

import into outline codes
can I import from Excel into Outline codes (not outline level) Hi Marylou, see my answer to your original post. Regards Barbara Am 03.03.2010 20:17, schrieb Marylou: > > can I import from Excel into Outline codes (not outline level) > Hi Barbara, thank you I did see your original post; however this was not my question. I already import outline levels. My question is: can I import into the customized outline code1; outline code2, etc thanks "Barbara - Austria" wrote: > Hi Marylou, > > see my answer to your original post. &g...

transferring money from one computer to another
Folks, I just got this laptop which I really like, but unfortunately, I can't figure out how to get my MS Money program (not the files-- that's a no-brainer) to my new computer. I downloaded the program and did not get the CD for it. Is there a way to do it? Both of these computers are mine, so I should be able to, but I can't figure it out. Thanks! Govind... There are almost no apps that can be moved from one machine to another these day. Almost all require having the installation routines. This is why I always recommend against "download" installations. &qu...

Code to Implement Required Cells Not Working
I have a worksheet called "Change Request Form" where users are required to input values in the following cells: C9 = CPM Full Name C10 = IT PM Full Name C11 = Change Type C12 = Reason Category C13 = Project Name C14 = Release C15 = PAT ID C16 = PRISM ID C17 = Explanation E15 = New PAT ID E16 = New PRISM ID I want to require users to provide input in most or all of the other aforementioned cells prior to Saving the workbook depending on the value of cell C11. I wrote the code shown below and put it in the ThisWorkbook object. Unfortunately, upon testing, u...

One-to-one relationship
I am importing a DB from Filemaker with info about students (names, address, etc.). There is just one table and because it has been used for many years, it cannot be changed. I have created another table for additional student enrollment information. I have created a one-to-one relationship between the two tables using an autonumber primary key in the student table and a number primary key in the enrollment table. I have created two forms to add information to the tables. All new students are added via the old student form first. When I switch to the new student form, I can...

mstsc fails to connect on one of two laptops
I have to nearly identical HP laptops. Both are the same model, both are running Vista64 Home Premium. With mstsc, I can connect to my XP Pro SP3 system with no trouble at all. But on the other one, the connection fails with an illuminating "Cannot connect" message. No details as to why. I tried copying the Default.rdb file from the machine that can connect to the other one. That made no difference. I tried connecting as a different user, creating another .rdp file, but that didn't work either. Any ideas? Thanks, Jason In article <#UUSfR4wKHA.3564@TK2MS...

Pull publishers up and only one out of ten pages shows up. Why?
I am using Publisher 2003. I made a booklet and had 60 some odd pages on it, but when I pull it up now only 1 page shows. Why? and what have I done or can do to correct this problem? ...

How do I merge 3 lines of text to show in one row?
I am doing a family history excel document. One row should have approx. 3 lines of info, however, I can only get one row of info to show. When I dclick that row the info shows but when I print or do a print preview only one line of info shows. I have merged the cells in that row. It sounds like you need to select Wrap Text from the Alignment tab of the Format Cells dialog and/or increase the height of the row to accommodate the numbers of rows of text in the cell. -- Ian -- "pita29" <pita29@discussions.microsoft.com> wrote in message news:E3D24CC3-7B38-4461-9B29-6...