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. 2 followers. Follow

4 Replies
703 Views

Similar Articles

[PageSpeed] 36

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:

Viewing series of appointments on one screen
Hello I teach at a college and use outlook at work and at home also. I am trying to set up a shared student outlook calender so that tutors can use this to place their lesson itinery/schedule into for each subject (the lessons could be added as recuring apointments). The lesson schedule is usually around 36 weeks - 1 lesson per week. Specifically I would like to know if its possible to isolate one of the recurring appointments in outlook and view all the series of appointments on one page. As each weekly lesson(appointment) contains unique content and is different from week t...

How do i sort contacts using more than one catagory ?
I am trying to create lists which have contacts sorted by more than one catagory. So including contacts that are linked to two or more specific catagories. Can this be done, if so how? thanks "samong" <samong@discussions.microsoft.com> wrote in message news:01E99022-AB21-487B-9365-2E66BB0C3F1B@microsoft.com... >I am trying to create lists which have contacts sorted by more than one > catagory. So including contacts that are linked to two or more specific > catagories. Can this be done, if so how? The Category field is non-sortable because it is a multi...

If Statement and Multiple Criteria
I am using a formula to bring back some text about some data. I am using the following formula: =IF(N12>M12,"is larger for girls","is larger for boys"), so that if the data in cell N12 is greater than the data in cell M12 then it says "is larger for girls" and vice versa. This works fine, apart from if negative numbers are in the cells then it brings back the value closest to zero - rather than the largest negative number. Does anyone know how to get round this. In addition - I would ideally like to alter the formula so that it says that if the two...

How to change multiple folders views in Outlook
When you first create an account within Outlook (currently using 2007 but I'm sure it's the same for other versions) it defaults the folders so that certain columns are visable they all have the viewing pane enabled. Is there a way of changing one folder so that all folders look identicle (Deleted, Drafts, Inbox, Junk E-Mail, Outbox etc etc etc). I'm sure there must be a way but I'd be damned if I can find it...and it's driving me nuts!! Cheers Glenn gloriousglenn@hotmail.com <gloriousglenn@hotmail.com> wrote: > Is there a way of changing one folder so that a...

find data in multiple open workbooks
Im looking to find data in multiple workbooks that are all opened at once. Is there a way of doing this? ...

One or more of your reminders can't be snoozed or dismissed
I am receiving the following reminder when attempting to dismiss a stale reminder that only appears whenever I access my Exchange email account via Outlook Web Access: "one or more of your reminders can't be snoozed or dismissed" This reminder is for a meeting that occurred a couple of months ago. It was deleted by me in Outlook at my workstation in the office and does not physically appear on the Calendar when using Outlook at the office. It DOES appear on the calendar when accessing via Outlook Web Access from the internet but is not deletable or modifiable in any way...

RPC over HTTPS on one server?
Hi! I want to install all only on one server: - Windows 2003 Active Directory - Exchange 2003 The users should be ablte to use RPC over HTTPS and other Exchnage 2003 services. Which steps must I follow for installation? Any good links about it? Best Regards On Wed, 9 Feb 2005 00:13:02 -0800, "mustafa" <mustafa@discussions.microsoft.com> wrote: >Hi! >I want to install all only on one server: >- Windows 2003 Active Directory >- Exchange 2003 > >The users should be ablte to use RPC over HTTPS and other Exchnage 2003 >services. > >Which steps mus...

Vlookup based on multiple criteria
I have 2 workbooks: Workbook one: Account number, name, January to September + total (columns A9 to L240) Workbook two: I need to populate with data from Workbook one Column A has the account numbers all accounts that contains department 71(xxxxx-71-xx) must go to location 1 (Jan - column B, Feb =96 column E etc) and all others goes to location 4 (Jan =96 column C, Feb =96 column F etc) Is there a basic a VLOOKUP or other function that can automatic the tasks? Thanks in advance. Workbook one Account Number Jan-10 Feb-10 Mar-10 41001-71-01 6,000.00 - 1,000.00 41001-71...

Formatting multiple object types in output from a function?
I'm stumped on this one, and have searched and searched for a solution or how-to with no luck. Perhaps it just can't be done. I have a function that may or may not return objects of multiple types. I've setup the type and formatting information for each of the individual types in format.ps1xml files, and when only 1 type is returned by the function the formatting works as I would expect. However, if two or more types are returned by the function, only the formatting for the first object type is applied and then the other types are displayed however PoSH deems ap...

counting using multiple criteria
Hi, I have a problem for which I can't seem to find a working solution. On the one hand I have a vertical table with employee names, the number of years they have been with the company, and all this sorted by the employee's age (in years). This table will be updated from time to time, and as such it doesn't have a fixed length. On the other hand I have a table which shows the number of employees in a particular age category (-20, 21-25, 26-30, etc) horizontally, and the number of years they have been with the company (again in groups: 0-5 yrs, 5-10 yrs, etc) vertically. ...

Entourage has stopped retrieving just one certain person's email
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Email Client: pop I have been receiving all my emails normally apart from just one sender, who i previously had no problems with. The email is sitting in my inbox when i log in via the browser account, however it just doesnt get sent directly from the sender. if I then send this person's email to myself, it gets delivered to my inbox. THe problem ahs only started in the last week, no settings have been changed and every other email is getting delivered. <br> My hosting provider says it is not their issue and they don...

Prinding each row of Excel sheet on a seperate page dispallying one record on each pa
From a spread sheet ,I need to print out a row on each page seperately that is printout each record on the row on one page seperately. Can any one please advise how it can be done ? Fro example Row 1 Data 11 Data 12 Data 13 Row 2 Data 21 Data 22 Data 23 Row 3 Data 31 Data 32 Data 33 I would want to print out Data 11 and Data 22 on one page then Data 21 and Data 22 on the second page and data 31 and Data 33 on the third page Pleae advise if and hwo this is possible. Thank you Sanjeev --- Message posted from http://www.ExcelForum.com/ Sorry ...

Template driver code for a new USB Touchscreen XP driver
We want to develop a new Touchscreen driver having a USB interface for Windows XP. Which driver template will be the most suitable to start the development ? make your device a HID and then you not have to write a driver at all. d -- This posting is provided "AS IS" with no warranties, and confers no rights. "sanju_emb" <sanju_emb@discussions.microsoft.com> wrote in message news:E8AAAA05-B6EA-44A7-B9E8-20E5F0A5860C@microsoft.com... > We want to develop a new Touchscreen driver having a USB interface for > Windows XP. Which driver te...

Is it possible to find multiple data?
We have a small lotterysyndicate in work which is all logged onto an excel spreadsheet. Is it possible to search all numbers and all occurances at once, rather than individually? Ie - we have 15 lines of 6 numbers each - can i search for all 6 numbers drawn, across all 15 lines in one go? Im sure this must be possible - just dont know how. Thanks -- stewwie ------------------------------------------------------------------------ stewwie's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=37128 View this thread: http://www.excelforum.com/showthread.php?threadid...

Error code 57E
I have Vista 32 bit XP windows Home Premium. Try to install 2007 Microsoft System KB972581 and Visio Viewer 2007 Service Pact 2, stalls at 50% install, however any other updates are installed. I have turned off the firewall and Microsoft Security Essentials and tried to install after, and still error code 57E on these 2 updates. The updates that did install are as follows... Cumulative Security Active X Killbits Microsoft Outlook 2007 Junk Mail Filter Security Essentials for Microsoft Office Windows Malicious Removal Tool Security Update I had this problem before with the SP2 ...

multiple entries in outlook 2003
i have one user who has mulitple entries of users showing in his contacts window. I am not talking double entries (one must be a business fax), but more like 8 or 9 entries all the same. I exported his contacts to a csv file to see if they have slight differences but no they seem to be exactly the same. So far hea is deleting old ones, but they keep coming back. Now for the fun bit. It is not all users multiplying, just about 50% of them. it seems to happen every time he logs on he gets another replication. He also has the issue that if he creates a new contact in his contacts window it...

can one cell contain more than one independent number
A cell can contain several numbers, separated by space characters or line breaks (Alt+Enter). However, if you want to perform calculations on the numbers, it's best to keep them in separate cells. John K wrote: -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html there's also the option a using matrix formula (list of data) for example you may enter ={1;2;3} in a single cell or selection 'and validate wih CTRL+SHIFT+ENTER but the manipulation of this kinda of formula need to read more about it... ...

.xls changes sizes from one computer to another
An excel worksheet that we use prints out nicely on one computer; however, when another user accesses it off of the network and does a print preview, the columns of the worksheet squash together in the middle of the worksheet into and only by reducing the view by 99% is the second user able to print correctly. I don't know the circumstances regarding this but I am told this is the way it has always been. Any input would be greatly appreciated. ...

Changing multiple appointment times
I have hundreds of appointments and would like to move them back one hour. I know there is a link in microsofts support site that lets you change the time zone without changing appointment times. However, recurring appointments are not supported by that method. Does anyone know of a way to move all my appointments back one hour? ...

Managing Calender's across Multiple Accounts
The CEO of my organization is actually the CEO of several companies, and as such, required several separate e-mail addresses(3). He also travels a lot and makes use of his Aircard to access the company network remotely with his laptop. So, I created an outlook profile for him on his laptop with all 3 Exchange accounts attached to it (on the same profile), but the network traffic was too much for the aircard to handle. So, I seperated them into 3 seperate Outlook profiles, and he needs to choose which to connect to when he logs in. This seems to work out well for him for the most part, wh...

Display Screen 1/2 Grid; 1/2 VBE (or at least Code Window)
Is there a way (button, command, etc) to be able to view (quickly, and side by side) a my worksheet (only 1 workbook is open say) and my code window? If you just want to look at macros do a cltr+' (key to left of 1). John North Yorkshire UK "Jim May" <jmay@cox.net> wrote in message news:Fqb7d.10064$0j.2648@lakeread07... > Is there a way (button, command, etc) to be able to view (quickly, and > side > by side) > a my worksheet (only 1 workbook is open say) and my code window? > > Jim With Excel workbook open to the sheet of your choice and VBE o...

Multiple Calendars
I have created multiple calendars for my boss, for different program components. He has the need to print these calendars frequently, but finds it extremely frustrating to have to change the header for each calendar he prints. I must say it seems obvious to me that there should be a way to have separate headers for each calendar, obviating the need to change the header for each different calendar printed. Another issue is the impossibility of emailing the calendars. We do not have ready access to a web server and I have not been able to save as html without a webserver. I tried e...

Conditional formatting formula with multiple criteria
Hi all, Have been puzzling over this for a while now, and can't get it right! Using conditional formatting, I want to: If any of the cells in Column B are blank, and the cells in Column C are blank, and the date in Column D is less than todays date - 3 days, turn the cell bold and red Can someone please help? I have tried all different variations and just can't get it! Thanks, Kirstie Kirstie Your questions sound like you wish to pick up whether there is a blank ANYWHERE in column B AND (not or) ALL cells in column C are blank AND that the date in a single cell in colum...

Autoexec macro code
Hope someone can help. Looking for suitable autoexec macro code to open my database automatically. My database does not have a switchboard. Not sure if I need one to do this. Thanks, regards, Paul. Hi Paul, If your database doesn't have a switchboard, what is it that you want to open automatically when your database opens? Try setting the startup properties if you want to open a specific form - right click on the database window and left click on "Startup". Hope this helps. Damian. "Paul" wrote: > Hope someone can help. > > Looking for suitable au...

Matching Zip coded in a Query
I have query that joins two tables using the Zip Codes (only show the records where the Zip codes match). The problem is that in one table, the listing of zip codes, it is 5 digit zip codes. In the main table some of the zip codes are 5+4 zip codes. My current query leave out the 5+4 Zip codes. I want all the records as long as the 5 digit zip codes matches. I quess I could delete the - and the last 4 digits on the main table, but it would be nice to retain the whole zip codes. Is there a way to do this? Thanks!!!!! Use Left(MainTableZipCodeField,5) to compare to the original ...