Macro copy paste + delete cell value

oviciro

New Member
Joined
May 14, 2019
Messages
4
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)
}
}

}
}
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,914
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

New Member
Joined
May 14, 2019
Messages
4
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

Well-known Member
Joined
Apr 11, 2012
Messages
8,914
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
 

oviciro

New Member
Joined
May 14, 2019
Messages
4
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

Well-known Member
Joined
Apr 11, 2012
Messages
8,914
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,096,444
Messages
5,450,488
Members
405,614
Latest member
SJ789

This Week's Hot Topics

Top