Macro copy paste + delete cell value

[oviciro]

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)
}
}

}
}

 

[oviciro]

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

 

[mumps]

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.

 

[oviciro]

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

 

[mumps]

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.

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

 

[oviciro]

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

 

[mumps]

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-announcements/127080-guidelines-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.