+ Reply to Thread
Results 1 to 15 of 15

VBA code to auto copy & paste special as value from target column

  1. #1
    Registered User
    Join Date
    08-31-2017
    Location
    LONDON
    MS-Off Ver
    Excel for Mac 2011 v14
    Posts
    20

    VBA code to auto copy & paste special as value from target column

    Hi

    I have a formula that pushes a value to the current month

    =IF(TEXT(TODAY(),"mmm")=A13,$C$21,0)

    Unfortunately once the month changes the input reverts to 0
    To resolve this I plan to do an auto copy & paste special as value to the adjoining column.
    The value will go to 0 in the original cell but the copied version should stay.

    To do this I need help please with some vba code
    I found something similar but it is not quite correct and I am a complete novice with vba code

    Any help appreciated

    It is all set out fairly clearly in the attached spreadsheet

    Many thanks
    Attached Files Attached Files

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA code to auto copy & paste special as value from target column

    Try this in the sheet module:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    08-31-2017
    Location
    LONDON
    MS-Off Ver
    Excel for Mac 2011 v14
    Posts
    20

    Re: VBA code to auto copy & paste special as value from target column

    Thanks - it works on the Trial sheet but does not work with an amendment to the Input Page. That is if I have entered it correctly. I have added a page called Input. Cell A1 holds the value. If I click on C21 in the Trial page, then click SUM Input page A1 Return it all works. But if I then go to the Input page and change the value to another Value it only pushes it to C21 and B21 remains as it was?

    Is it possible to make this work. What I am after is being able to put value in A1 on the Input sheet and this then goes via C21 to relevant current month On Trial page. So it is very nearly there. Maybe I need a formula to push Input A1 to Trial C21, rather than using the linked Sum method?

    Any help appreciated.

    I have uploaded the spreadsheet with the vba in it.
    Attached Files Attached Files

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA code to auto copy & paste special as value from target column

    Hi,

    Thanks for the rep!

    Put this in the Input sheet module:

    Please Login or Register  to view this content.
    Do you also want it in Column C??

  5. #5
    Registered User
    Join Date
    08-31-2017
    Location
    LONDON
    MS-Off Ver
    Excel for Mac 2011 v14
    Posts
    20

    Re: VBA code to auto copy & paste special as value from target column

    Thanks

    I must be doing something wrong as on mine (attached herewith) it doesn't flow through to B13 on Trial page.

    When I input the vba I see there is a drop down on far right hand side - I assume I set that to 'Activate'?

    Do I need to make a button?

    Thanks
    Attached Files Attached Files

  6. #6
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA code to auto copy & paste special as value from target column

    I don't get it - it works for me using your upload - or are we having a failure to communicate!

  7. #7
    Registered User
    Join Date
    08-31-2017
    Location
    LONDON
    MS-Off Ver
    Excel for Mac 2011 v14
    Posts
    20

    Re: VBA code to auto copy & paste special as value from target column

    Hi - on my previous upload, if you change the value on the Input page in Cell A1 from say 45 to 23
    23 will appear in C21 and C13 on Trial page, but not flow through to B21
    Do I need to do something to activate the vba on Trial page or should it just work?

    Thanks

  8. #8
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA code to auto copy & paste special as value from target column

    It should just work - it worked here (You mean B13 - right???)


    Here it is:
    Attached Files Attached Files
    Last edited by xladept; 09-03-2017 at 11:12 AM. Reason: attach

  9. #9
    Registered User
    Join Date
    08-31-2017
    Location
    LONDON
    MS-Off Ver
    Excel for Mac 2011 v14
    Posts
    20

    Re: VBA code to auto copy & paste special as value from target column

    Hi

    I downloaded that version
    If I go to page Input and enter say 71 then return
    Then go back to the page Trial, 71 goes into the cell C21 and C13, but B13 stays as it was!

    I have no idea why not?!

  10. #10
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA code to auto copy & paste special as value from target column

    I'm confounded - the 71 goes into C21 and Thus B13 it doesn't go into C13???

  11. #11
    Registered User
    Join Date
    08-31-2017
    Location
    LONDON
    MS-Off Ver
    Excel for Mac 2011 v14
    Posts
    20

    Re: VBA code to auto copy & paste special as value from target column

    Hi
    See screen shot
    Thanks

  12. #12
    Registered User
    Join Date
    08-31-2017
    Location
    LONDON
    MS-Off Ver
    Excel for Mac 2011 v14
    Posts
    20

    Re: VBA code to auto copy & paste special as value from target column

    Screenshot

  13. #13
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA code to auto copy & paste special as value from target column


  14. #14
    Registered User
    Join Date
    08-31-2017
    Location
    LONDON
    MS-Off Ver
    Excel for Mac 2011 v14
    Posts
    20

    Re: VBA code to auto copy & paste special as value from target column

    Hi - See screen shot I have changed from Tiff to Jpeg

    B13 doesn't get changed ? At least on my version
    Attached Images Attached Images

  15. #15
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: VBA code to auto copy & paste special as value from target column

    Are you using the last one I uploaded?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Tweak Code for Copy and Paste Special vs Cut and Paste
    By Adam Schaefer in forum Excel General
    Replies: 1
    Last Post: 04-18-2016, 11:12 AM
  2. VBA: code check to auto copy and paste into new column
    By puekey in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-09-2014, 12:43 AM
  3. [SOLVED] Code to Copy Rows from 3 zones based on min/max value,and paste in next empty Target row
    By david gonzalez in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-08-2014, 02:29 AM
  4. Macro Code to Copy paste Special
    By cartica in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-04-2013, 09:31 AM
  5. [SOLVED] Set code copy and paste special values
    By marreco in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-25-2012, 08:52 PM
  6. [SOLVED] Code for Copy and Paste Special- Values in Columns
    By gautamacharya in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-23-2012, 04:51 PM
  7. code to copy and paste special and sort
    By short_n_curly in forum Excel General
    Replies: 0
    Last Post: 03-29-2005, 09:35 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1