Need to copy a row from Sheet1 to Sheet2 then auto delete from Sheet 1 based on Column G

**jingles9** · 10-16-2015, 02:21 PM

Hello Everyone,
I have an excel spreadsheet (Excel 2007). I need to copy the entire row from the Sheet labelled "Master" based on the comment placed in column G labelled "Status". If the comment states "Closed", I want the entire row to be transferred to the sheet labelled "Closed" and then the original row from the "Master" to automatically delete.
Currently my excel file will transfer the information as required but it will not delete it from the "Master" tab. On the sample it is Row 8 on the "Master" tab that I want to have auto delete.
Does anyone know how I can do this? Any help is greatly appreciated.

**mehmetcik** · 10-16-2015, 02:35 PM

I would use two macros to do this.

The first sheet specific would look for the word closed and run the second macro.

Right click on the sheet name at the bottom of excel and select view code

paste this code there and close the window.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or Target.Column <> 7 Or InStr(LCase(Target.Value), "closed") = 0 Then Exit Sub
Closed (Target.Row)
End Sub

Put this second Macro into a standard Macro Module.


Sub Closed(MyRow As Integer)
Application.EnableEvents = False

With Sheets("Closed")
    .Range("A2:H2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End With

    Sheets("Master").Range("A" & MyRow & ":H" & MyRow).Copy Destination:=Sheets("Closed").Range("A2")
    Sheets("Master").Range("A" & MyRow & ":H" & MyRow).Delete Shift:=xlUp
Application.EnableEvents = True
End Sub

**jingles9** · 10-16-2015, 03:20 PM

Hi Mehmetcik...thanks for the quick reply. I tried to follow your instructions but I cannot get it to work. I deleted the IFERROR formulas on the "Closed" tab, but that didn't seem to work either. I also noticed there was coding in Module 1 which I no longer needed so I deleted that as well. Any suggestions on what I may be doing wrong? Thanks again for looking at this.

**mehmetcik** · 10-16-2015, 04:13 PM

Sample Attached.

**jingles9** · 10-17-2015, 08:40 PM

Works perfectly....thanks so much

**jingles9** · 10-20-2015, 08:11 AM

One last question....Currently I have coding on the first tab that will auto sort the sheet once it is activated (select closed tab and then go back). I tried pasting this coding on the "Closed" sheet as well but it didn't work. I also tried pasting it into the "Worksheet" coding but still no luck. Is there a way to have the "Closed" sheet auto sort alphabetically by Column A as well?

Need to copy a row from Sheet1 to Sheet2 then auto delete from Sheet 1 based on Column G

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Need to copy a row from Sheet1 to Sheet2 then auto delete from Sheet 1 based on Column G

Re: Need to copy a row from Sheet1 to Sheet2 then auto delete from Sheet 1 based on Column

Re: Need to copy a row from Sheet1 to Sheet2 then auto delete from Sheet 1 based on Column

Re: Need to copy a row from Sheet1 to Sheet2 then auto delete from Sheet 1 based on Column

Re: Need to copy a row from Sheet1 to Sheet2 then auto delete from Sheet 1 based on Column

Re: Need to copy a row from Sheet1 to Sheet2 then auto delete from Sheet 1 based on Column

Thread Information

Users Browsing this Thread

Similar Threads

copy sheet 2 data as it is in sheet3 if column "B" matches in sheet1 & Sheet2

Need a macro to delete data on sheet2, copy data from sheet1 to sheet2

[SOLVED] copy data from sheet1 to sheet2 based on column (A) sheet1 and column (C) sheet2

Insert/Delete record in Sheet2 based on cell value in Sheet1

match data of sheet1 and Sheet2 ColumnA, if matched copy columnB of sheet1 to sheet2

Copy certain data from Sheet1 to Sheet 2 based on values in a column on Sheet1

[SOLVED] Copy and Paste Entire Row from Sheet1->Sheet2 based on text string match in Sheet1 Row

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions