+ Reply to Thread
Results 1 to 6 of 6

Copy Paste data from cell range to another

  1. #1
    Registered User
    Join Date
    03-23-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    12

    Copy Paste data from cell range to another

    Hi All,

    First off, very basic knowledge/use of macros.

    I have data in Columns C10:C12, which is based off data from other worksheets (eg. =Sheet2!$C$5).

    Now, I want to move this data to D10:D12, but when I do this, the formula reference gets deleted as well (eg. =Sheet2!$C$5)

    Below is the code:

    Sub ClearCurrentData()
    Range("C10:C12").Copy Range("D10") 'COPY FROM C AND PASTE TO D
    Range("C10:C12").Copy 'COPY COL C
    'PASTE THE VALUES TO RIGHT IN LAST BLANK COLUMN TO RIGHT OF I10

    Range("I10").End(xlToRight).Offset(0, 1).PasteSpecial xlPasteValues

    'CLEAR CONTENTS OF C
    Range("C10:C12").ClearContents

    'ACTIVATE THE 1ST CELL
    Range("C10").Select
    End Sub

    Question is: any way i can do this without the formula being deleted? Using excel 2016

    Any help would be appreciated

    thank you

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Copy Paste data from cell range to another

    Your macro will paste the formula from C10:C12 to D10:D12 but not to the last blank column to the right of I10. Is the formula in D10:D12 being deleted?
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Valued Forum Contributor dmcgov's Avatar
    Join Date
    11-11-2015
    Location
    Florida, USA
    MS-Off Ver
    Office 365 Business
    Posts
    1,518

    Re: Copy Paste data from cell range to another

    so not sure what your doing here but if you don't want the reference to be deleted, then do the following

    substitute this:

    Please Login or Register  to view this content.
    for this

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    03-23-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    12
    Quote Originally Posted by Mumps1 View Post
    Your macro will paste the formula from C10:C12 to D10:D12 but not to the last blank column to the right of I10. Is the formula in D10:D12 being deleted?
    Thanks for your reply. I am trying to copy data from C10:12 to 2 locations.
    First location is D10:12 and second location is the first empty columns to the right of I10. The macro is copying the data in the intended locations fine.
    However, the formulas in the source cells (C10:12), which are getting values from different sheets in the workbook, are being deleted as well.
    Based on the above code, is there any way I can avoid this from happening (ie not losing the formula reference in cells C10:12)?

  5. #5
    Valued Forum Contributor dmcgov's Avatar
    Join Date
    11-11-2015
    Location
    Florida, USA
    MS-Off Ver
    Office 365 Business
    Posts
    1,518

    Re: Copy Paste data from cell range to another

    Quote Originally Posted by mridul127 View Post
    Thanks for your reply. I am trying to copy data from C10:12 to 2 locations.
    First location is D10:12 and second location is the first empty columns to the right of I10. The macro is copying the data in the intended locations fine.
    However, the formulas in the source cells (C10:12), which are getting values from different sheets in the workbook, are being deleted as well.
    Based on the above code, is there any way I can avoid this from happening (ie not losing the formula reference in cells C10:12)?
    does my code not work for you?

  6. #6
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Copy Paste data from cell range to another

    Your macro deletes everything in C10:C12 including the formulas. You can use dmcgov's suggestion to insert the formulas back into C10:C12, however, since the formula has been re-inserted, that range will again populate with the value of C5 in Sheet2. If you want to keep the formulas but have C10:C12 return blanks cells, you will have to delete the value in C5 of Sheet2. This will return a zero. If you want blank cells, the formula would have to be changed to =IF(Sheet2!$C$5="","",Sheet2!$C$5)

+ 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 range and paste next to each cell with data
    By maym in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-10-2016, 02:13 AM
  2. [SOLVED] Copy/paste data using checkbox-clear when unchckd & multiple selections paste in next cell
    By Staceymcw in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-02-2015, 12:09 AM
  3. [SOLVED] Macro to copy and paste new data into the next blank cell WITHIN a range
    By MattRNR in forum Excel Programming / VBA / Macros
    Replies: 32
    Last Post: 10-22-2015, 12:45 AM
  4. [SOLVED] Copy Data from one sheet and paste the consolidated data into another (Data Range Varies)
    By kittu55 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-13-2013, 10:02 PM
  5. Copy/Paste range of data excluding certain rows and copy to another workbook
    By HoerbigAdm in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-12-2012, 02:51 PM
  6. Change range of cells within VBA macro and copy and paste to fixed cell range
    By Mannyny in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-20-2012, 11:51 AM
  7. How to copy/paste range only if data in ANY cell of that range
    By bridie4 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-22-2011, 08:13 AM

Tags for this Thread

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