+ Reply to Thread
Results 1 to 5 of 5

How to delete the exiting data before pasting the new data in a worksheet

  1. #1
    Registered User
    Join Date
    05-16-2016
    Location
    bangalore,India
    MS-Off Ver
    2013
    Posts
    32

    How to delete the exiting data before pasting the new data in a worksheet

    Hello,

    I want to delete the existing data before i paste the new data in a workbook. So how do i do it?

    I am using 2 workbooks.

    workbook 1: has the data from which i will copy.

    data.PNG


    workbook 2 :is were i paste the data.


    tb1.PNG


    i am able to copy and paste the data. But the problem i am facing is that workbook 2 already consists of some data in it and when i paste the data in it then it gets added up with the existing data, so how do i delete the existing data before pasting the new data.

    Below is the code:

    Windows("kl.xls").Activate 'workbook1
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$H$16").AutoFilter Field:=8, Criteria1:="US"
    Range("A2:G2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

    Windows("tecdata.xlsx").Activate 'workbook2
    Sheets("Sheet1").Select
    Range("A3").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False


    I have attached both the workbooks.


    Regards
    Wither
    Attached Files Attached Files
    Last edited by Wither125; 05-24-2016 at 04:58 PM.

  2. #2
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: How to delete the exiting data before pasting the new data in a worksheet

    Try next code
    Note "c250k.xlsm" has been changed to "tecdata.xlsm"
    Please Login or Register  to view this content.
    - Battle without fear gives no glory - Just try

  3. #3
    Registered User
    Join Date
    05-16-2016
    Location
    bangalore,India
    MS-Off Ver
    2013
    Posts
    32

    Re: How to delete the exiting data before pasting the new data in a worksheet

    Hello PCI,

    Thank you its working.

    can u pls help me with this.

    i want to apply the formula for the below rows of column H.

    tat.PNG

    The formula is F3 - E3 and this formula is to be applied to the column H. Also how do i round down the number in column H.

    Below is the code:

    ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-3]"
    Range("H3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    ActiveCell.Activate
    ActiveSheet.Paste


    Its applying the formula only to H3 not to the below rows, as u can see the above pic.
    Attached Files Attached Files
    Last edited by Wither125; 05-24-2016 at 05:58 PM.

  4. #4
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: How to delete the exiting data before pasting the new data in a worksheet

    Wither125,
    Open a new thread, it is another question and it may confusion those who would like to reuse you code
    PCI

  5. #5
    Registered User
    Join Date
    05-16-2016
    Location
    bangalore,India
    MS-Off Ver
    2013
    Posts
    32

    Re: How to delete the exiting data before pasting the new data in a worksheet

    HI PIC,

    i have created the new thread.

+ 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. Auto delete all previous version of the worksheet when exiting updated sheet
    By Robbosan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-20-2016, 06:39 AM
  2. [SOLVED] Code to delete data prior to pasting new data is REALLY slow
    By Reapz in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-19-2015, 04:41 PM
  3. Update the worksheet row data after matching the details of exiting data of row.
    By sandeep_sarode in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-05-2014, 02:17 AM
  4. Replies: 6
    Last Post: 10-09-2013, 06:12 AM
  5. [SOLVED] Macro to Delete Data in one worksheet based of the data that is in another worksheet.
    By shortma in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-18-2012, 10:52 AM
  6. Copying/pasting data & delete row up according to criteria
    By D_N_L in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-07-2012, 06:45 AM
  7. Automatically pasting worksheet data to new worksheet with formulas
    By COntactJason in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-10-2005, 03:22 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