Results 1 to 7 of 7

Thread: Macro copy paste + delete cell value
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Macro copy paste + delete cell value

    Hello evryone,
    i have created an app script that i need to convert in bva macro and im no expert with excel. would anyone here help me please? the followin is the code i did on app script... basically it says any cell on column "c" that has value for example the word EXIT, then copy cell from same row column "B" and paste on column "P" and column "D" copy and paste on column "P" same row then delete those cells it was copy from these appscript has more things to it if someone can help me write it on bva macro i would really apreciated.

    function onEdit(e) {
    if(e.range.columnStart === 3) {
    if(e.range.rowStart > 1 && e.range.rowStart < 41) {
    if(e.value == 'salida') {
    e.range.offset(0, 17).setValue(e.range.offset(0, -1).getValue());
    e.range.offset(0, 19).setValue(e.range.offset(0, 3).getValue());
    e.range.offset(0, 18).setValue(e.range.offset(0, 1).getValue());
    e.range.offset(0, -1, 1, 2).clearContent();
    e.range.offset(0, 3, 1, 14).clearContent();
    e.range.offset(0, 1).clearContent();
    e.range.offset(0, 2).clearContent();
    }
    }
    }




    if (SpreadsheetApp.getActive().getActiveSheet().getRange("e1").getValue() == 'borrar') {
    var ss = SpreadsheetApp.getActiveSpreadsheet ();
    ss = SpreadsheetApp.getActiveSheet().getRange("f3:f40").clearContent();
    ss = SpreadsheetApp.getActiveSheet().getRange("t3:v40").clearContent();
    ss = SpreadsheetApp.getActiveSheet().getRange("e1").clearContent();
    }




    if(e.range.columnStart === 3) {
    if(e.range.rowStart > 1 && e.range.rowStart < 41) {
    if(e.value == 'llegada o cambio'){
    var stampCol = e.range.offset(0, 4)
    if (!stampCol.getValue()) {
    v = new Date()
    }
    stampCol.setValue(v)
    }
    }

    }
    }

  2. #2
    New Member
    Join Date
    May 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro copy paste + delete cell value

    im sorry i mean to say column D copy en paste to column Q same row

  3. #3
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    7,858
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Macro copy paste + delete cell value

    Rather than trying to decipher your app script, could you explain in words what you are trying to do referring to specific cells, columns and sheets using examples form your data? What are the words that you are looking for in column C? If you could post a screen shot of what your data looks like, it would help.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  4. #4
    New Member
    Join Date
    May 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro copy paste + delete cell value

    Hello mumps,
    thanks for replying...
    well lets say i like to be able to copy and paste any cell from "B and "D" column if in cell c says "exit" and paste on "P" and "Q" everything within the same row.

    example range row will be from 1 to 40 but i will write "EXIT" on C3 and B3 and D3 will be copy and paste B3 on P3 and D3 on Q3 the delete everythin from B3:M3

    sorry this is the best i can explain it
    thanx in advance

  5. #5
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    7,858
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Macro copy paste + delete cell value

    This macro assumes that you have headers in row 1 and your data starts in row 2. Keep in mind that it will work only for the text "EXIT". If you want to use a different word or multiple words, the macro will need to be modified.
    Code:
    Sub copyCells()
        Application.ScreenUpdating = False
        Dim LastRow As Long
        LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        With Cells(1, 1).CurrentRegion
            .AutoFilter 3, "EXIT"
            Range("P2:P" & LastRow).SpecialCells(xlCellTypeVisible).Value = Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible).Value
            Range("Q2:Q" & LastRow).SpecialCells(xlCellTypeVisible).Value = Range("D2:D" & LastRow).SpecialCells(xlCellTypeVisible).Value
            Range("B2:M" & LastRow).SpecialCells(xlCellTypeVisible).ClearContents
            .AutoFilter
        End With
        Application.ScreenUpdating = True
    End Sub
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  6. #6
    New Member
    Join Date
    May 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro copy paste + delete cell value

    Hello mumps,

    thank you for the help but the code is not working it says there is en error with the Autofilter. every time i try to run it a window comes up and it gives me the option to depure or to end it and then it highlights (.AutoFilter 3, "EXIT"). and a box says error 1004

  7. #7
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    7,858
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Macro copy paste + delete cell value

    When I tested the macro on some dummy data, it worked properly. Can you post a screen shot of what your data looks like? Section B at this link has instructions on how to post a screen shot: https://www.mrexcel.com/forum/board-...forum-use.html Alternately, you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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