+ Reply to Thread
Results 1 to 8 of 8

Copy cells and paste like a cut, no adjusting for movement

  1. #1
    Forum Contributor
    Join Date
    03-05-2007
    Location
    Palm Bay, FL, was Colorado Springs, CO for ten years!
    MS-Off Ver
    365
    Posts
    538

    Copy cells and paste like a cut, no adjusting for movement

    Yes, that pretty well says it all.

    Is there some possible way to control-C type copy a range from a sheet, then paste it duplicating everything from the original cheet; i.e., exact same cell references as are in the cell formulas?

    I am suspicious it is right in front of me and I can;t see it, but I have run through all the special pastes that I could from the left-click paste sub-menus and I can not find it!

    Thank you for continuing to preserve what sanity I have left...(!)

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Copy cells and paste like a cut, no adjusting for movement

    If I understood you correctly, which may not be the case.. you can copy the entire range from the column or row and paste it back by Paste Special, Values only. This will leave values and remove formulas.

    If this is not what want, I suggest you upload a sample spreadsheet

    To upload a file click on Go Advanced on the bottom of your screen, then click on paperclip icon and Add Files.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Valued Forum Contributor Sean Thomas's Avatar
    Join Date
    03-25-2012
    Location
    HerneBay, Kent, UK
    MS-Off Ver
    Excel 2007,2016
    Posts
    971

    Re: Copy cells and paste like a cut, no adjusting for movement

    It depends what you want to do with the data

    you can either use the paste link option in paste special, but this will not copy formulas, just links each cell to the original ones.
    If you just want to display the same data/results and have them up dated each time they are changed in the original sheet then you could look at using the camera tool. But again this does not show the original formulas.
    Regards
    Sean

    Please add to my reputation if you think i helped
    (click on the star below the post)
    Mark threads as "Solved" if you have your answer
    (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code:
    [code] Your code here [code]
    Please supply a workbook containing example Data:
    It makes its easier to answer your problem & saves time!

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,013

    Re: Copy cells and paste like a cut, no adjusting for movement

    If you absolute the references in teh original formula, then you can copy it wherever and the references will not change. Or you could create named ranges to use in the formula to replace the actual ranges
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Forum Contributor
    Join Date
    03-05-2007
    Location
    Palm Bay, FL, was Colorado Springs, CO for ten years!
    MS-Off Ver
    365
    Posts
    538

    Re: Copy cells and paste like a cut, no adjusting for movement

    It is an area that has both values and formulas.

    As you know, if we copy cell A1 with the formula =A1 + 5 to cell A2, the formula changes in cell A2 to be =A2 + 5

    Now, if you CUT A1 and paste it to A2, the formula remains the same, being =A1 + 5

    I want to COPY A1 so everything REMAINS there, paste it into A2, and have the formula remain the same, =A1 +5, but instead of one cell, I need to do it with a range of roughly 15 rows by 10 columns, otherwise, I would simply go into the formula window for a cell, copy the characters there and paste them into the destination.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,013

    Re: Copy cells and paste like a cut, no adjusting for movement

    As you know, if we copy cell A1 with the formula =A1 + 5 to cell A2, the formula changes in cell A2 to be =A2 + 5
    This is true, but if we copy cell A1 with the formula =$A$1 + 5 to cell A2, the formula in cell A2 stays =$A$1 + 5

  7. #7
    Forum Contributor
    Join Date
    03-05-2007
    Location
    Palm Bay, FL, was Colorado Springs, CO for ten years!
    MS-Off Ver
    365
    Posts
    538

    Re: Copy cells and paste like a cut, no adjusting for movement

    It is a table (edit: as I wrote later on, a better word for "table" would be "matrix", for it is NOT an Excel structured table)of references to items on other sheets. Some are totally fixed (as in $A$1), some just the column (as in $A1), some just the row (as in A$1), and some not at all (ok... A1).
    If it would help clear up any confusion - an example; just enter:
    dog in A1
    cat in A2
    bird in A3
    frog in A4

    Now in C1 type =A1
    Drag copy C1 through C4 so ending in C4 we have =A4

    Now cut C1:C4 and paste in D1. All references have remained pointing to A1 through A4.

    If instead you COPY C1:C4 and paste in D1, instead of D1:D4 pointing to A1:A4 the cells now point to B1:B4. I need a COPY that will act like the cut & paste did, but leave all REFERENCES pointing to A1:A4 the same AND will copy all formats and everything else.

    Presume that we can not play with throwing in fixed $ references any more or different than already exist (and a LOT exist), because the table (a table in appearance, NOT an Excel structured table - perhaps a better word would be "matrix") is complicated enough and has internal dependencies that make throwing in a bunch more of dollar signs worse than having to copy each cell's formula, cell by cell (Well, it would be about the same PITA). (Note: It might be possible to work with - if there is a way to highlight the entire matrix and convert all to absolute references (I am misusing the term "absolute" I think; I suspect the correct word is "fixed" - i.e.; to make EVERY cell a fixed reference with two $ as in $A$1).

    I can't understand why there is not a natural ability to do so; therefore, I have to believe I am missing something obvious! Wouldn't be the first time...

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,013

    Re: Copy cells and paste like a cut, no adjusting for movement

    OK maybe 2 more approaches to look at then?

    1. as mentioned above, use names ranges instead of cell references (not sure how practical that would be if you have a lot of different formulas/cells?)
    2. Use INDIRECT() to completely "fix" the reference?

+ 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. adjusting if/match formula to allow positive and negative movement
    By neowok in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-17-2013, 04:13 AM
  2. [SOLVED] VBA to Copy set data, paste into set cells, then copy more & set into cells related 2 date
    By galvinpaddy in forum Excel Programming / VBA / Macros
    Replies: 99
    Last Post: 02-01-2013, 08:43 AM
  3. Replies: 1
    Last Post: 01-16-2013, 05:36 AM
  4. Copy and paste non consecutive cells and paste consecutively in another workbook
    By macquhele in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-12-2011, 02:36 PM
  5. Replies: 8
    Last Post: 02-14-2005, 11:06 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