+ Reply to Thread
Results 1 to 4 of 4

Code to prevent copy & pasting values from one tab to another

  1. #1
    Registered User
    Join Date
    07-13-2021
    Location
    CT_USA
    MS-Off Ver
    Microsoft Office Standard 2016
    Posts
    9

    Code to prevent copy & pasting values from one tab to another

    So here is what I am currently doing, but would like to simplify it with a formula if possible.

    I have an excel file that is converted from a PDF. For this example the file name is '001.XML'

    I have another file named '10pcs_123_JULY2021.XML'. This file has three tabs, FORMULAS, SN, DATA ONLY. The DATE ONLY tab has charts that compile the data and rules to look for values greater or less than specific numbers. (This file is submitted to my customer.)

    Right now I take a list of Serial Numbers and enter them into to SN tab and sort by low to high. In the FORMULAS certain cells copy the serial numbers from the SN tab and other cells have a formula to retrieve values from another excel file when that file is open. Once this file (example, excel file '001.XML') is open the values pop up into specific cells in under the FORMULAS tab in the '10pcs_123_JULY2021.XML' file. I then copy those cells, go to the DATA ONLY tab and paste the values. Once the '001.XML' file is closed the values from the FORMULA tab say #REF! and the values in the DATA ONLY tab remain. This is exactly what I want.

    What I would like to is a code and/or shortcut that will 'lock' those values into the DATA ONLY tab, so I can eliminate the copy and paste values step. Any ideas?


    Thanks in advance,
    Marc
    Last edited by MARC_IN_CT; 07-13-2021 at 08:18 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,332

    Re: Code to Paste VALUES...BUT......

    Welcome to the forum.

    I am moving this to the VBA section.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,332

    Re: Code to Paste VALUES. & prevent copy & pasting thereafter

    Administrative Note:

    We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. As you are new I have done it for you today.)

  4. #4
    Registered User
    Join Date
    07-13-2021
    Location
    CT_USA
    MS-Off Ver
    Microsoft Office Standard 2016
    Posts
    9

    Re: Code to prevent copy & pasting values from one tab to another

    Is my title ok? Would this be a VBA function?

+ 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] Copy/Paste Values Using VBA Code
    By DanielGordo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-19-2014, 09:51 AM
  2. [SOLVED] Code to paste values from different workbook
    By kosherboy in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 06-16-2014, 04:33 PM
  3. [SOLVED] Using code to paste values
    By kosherboy in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 06-12-2014, 11:36 AM
  4. Code to paste values not formulas
    By excellearner121 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-22-2014, 06:33 AM
  5. [SOLVED] Changing Current Code from Paste Formulas to Paste Values
    By freybe06 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-24-2013, 12:33 PM
  6. Copy and paste code pasting won't paste just values
    By thehotbreadguy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-09-2013, 11:12 PM
  7. Changing code to paste as values
    By rhudgins in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-25-2010, 09:18 PM

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