Copying the entire row using VBA Closed

Question

Hi! 
I need a code that will automatically copy the entire row to another sheet (same file) when a condition has been satisfied.

See the sample below

A             B                C               D                E            F
info1      info2          info3         info4           info5      Production
info1      info2          info3         info4           info5      Dispatched
info1      info2          info3         info4           info5      Completed
info1      info2          info3         info4           info5      Rejected
info1      info2          info3         info4           info5      Hold/Cancelled
info1      info2          info3         info4           info5      Tracking No

If, I fill up the column F with "Production", the entire row should be copied in the Sheet named Production. The same with Dispatched, Completed and so on...

Thanks in advance for your help.

venkat1926 · Answer

introduce the first row as headings row in sheet 1 hdng1 hdng2 hdng3 hdng4 hdng5 hdng6 info1 info2 info3 info4 info5 Production info1 info2 info3 info4 info5 Dispatched info1 info2 info3 info4 info5 Completed info1 info2 info3 info4 info5 Rejected info1 info2 info3 info4 info5 Hold/Cancelled info1 info2 info3 info4 info5 Tracking no 2 macros and one function are given elow run only "test" undo macro is remove the effect of macro Sub test()Dim r6 As Range, rdata As Range, filt As Range, cfilt As Range, xDim j As IntegerApplication.ScreenUpdating = FalseApplication.DisplayAlerts = FalseWith Worksheets("sheet1")Range(.Range("A1").End(xlDown).Offset(1, 0), .Cells(Rows.Count, "A")).EntireRow.DeleteSet r6 = Range(.Range("F1"), .Range("F1").End(xlDown))Set filt = .Range("A1").End(xlDown).Offset(5, 0)Set rdata = .Range("A1").CurrentRegionr6.AdvancedFilter xlFilterCopy, , filt, TrueSet filt = Range(filt.Offset(1, 0), filt.End(xlDown))For Each cfilt In filtx = cfiltrdata.AutoFilter field:=6, Criteria1:=xrdata.Offset(1, 0).Resize(rdata.Rows.Count - 1).SpecialCells(xlCellTypeVisible).CopyOn Error Resume NextIf Exists(Worksheets(x)) ThenGoTo nextstepElseWorksheets.AddActiveSheet.Name = xEnd Ifnextstep:With Worksheets(x).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecialEnd With.AutoFilterMode = FalseNext cfiltWith Worksheets("sheet1")Range(.Range("A1").End(xlDown).Offset(1, 0), .Cells(Rows.Count, "A")).EntireRow.DeleteEnd WithMsgBox "macro done"Application.ScreenUpdating = TrueApplication.CutCopyMode = FalseApplication.DisplayAlerts = TrueEnd WithEnd Sub Function Exists(sh As Worksheet)Dim ws As WorksheetOn Error Resume NextSet ws = shIf Err.Number <> 0 ThenExists = FalseElseExists = TrueEnd IfEnd Function Sub undo()Application.DisplayAlerts = FalseDim j As IntegerFor j = Worksheets.Count To 1 Step -1If Worksheets(j).Name <> "Sheet1" Then Worksheets(j).DeleteNext jWith Worksheets("sheet1")Range(.Range("A1").End(xlDown).Offset(1, 0), .Cells(Rows.Count, "A")).EntireRow.DeleteEnd WithEnd Sub

venkat1926 · Answer

I have sent you the complete workbook with macros in vb editor 
download the workbook from this web page

http://speedy.sh/3894u/scully-140313.xlsm


save  the file and close 
open
enable macros 
alt + F11
you will see the macro s(There was small mistake and I have modified -ignore old macros)

run "test" macro

give feedback whether you got what you want

remember your F6 hold/cancelled. a sheet name cannot have "/" so I changed to 
hold cancelled

venkat1926 · Answer

I checked in my computer it works(normally I do send the macro unless if test it and find results ok. 

you send your file (do not use the web url I have sent) do fresh.  using speedyshare.com and send the url for download I shall check .

by the by what is your version of excel.

venkat1926 · Answer

likely to be some ambiguity

consider this scenario 
sheet "LIST"
in row 5 the status is ready that mean you typer ready in U5
you copy to row 5 in the sheet "ready" in row no. 5
in row 7 status is rady U7 is ready row 7 copied to sheet ready in row no. 5

ok
now in "list" row no. 7 status is approved. that U7 is "approved"
this row should be copied to sheet despatched in row no. 5
now corresponding row in sheet "ready" should be delete. which tis row in
sheet ready row 5 ro r0w 6 because in boththe rows are same.

that is because in sheet list you have given info inro same in all the cells and date 
cells are same.  this may not be so. so your sheet list must be more repreentative.
ofcourse you can use random data for security reasons but the data should be different 
between rows in sheet list

do you get me?

Scully · Answer

Hi venkat1926,

Thank you for your quick response.
here is the new link with the more information.

http://speedy.sh/4NXzf/Workbook.xlsx

I think you have understood my request.
Just to be consistent, please don't included in the Status "Approved".
We should stick with, Production, Dispatched, Ready, Rejected and Hold Cancelled.

In summary, all the rows in the "List" should only appear on Production, Dispatched, Ready, Rejected and Hold Cancelled sheets one at a time.
If the status is "ready", the entire row should be copied in the Ready sheet. But the moment I change the status to "Dispatched", then the row in the Ready sheet will be deleted and the same will be copied in the Dispatched sheet.

Thank you so much for your time and let me know if you have any further clarification.

Regards!

venkat1926 · Answer

download the file from following url http://speedy.sh/5sMQ8/scully-second-version-140331-macro-enabled-Workbook.xlsm right click tab of sheet "List" and click view code( altenately alt+F11) you see the code this is an event code as soon as you type any caption in column U (where there is data in that row) then event code will be fired and the row will be copied in the relevant sheet if the string entered "dispatched" the row w2ill be in that sheet and that row (reference is column R) will be deleted from 'ready" sheet if you contiuously type and confirm an entry (F2 and enter) new line will be copied which will be dplicate. SO LONG AS YOU ARE TESTING RUN UNDO MACRO AND THEN ENTER ENTRY IN COL u OF lIST. BUT ONCE TESTING IS OVER DO NOT DO UNDO MACRO use with desicretion ALWAYS MAKE SURE THAT THE ENTRIES ARE MADE IN SHEET "lIST" ONLY . IF YOU DO THIS BY MISTAKE IN ANYSHEET NOTHING WILL HAPPEN AND WHATEVER YOU TYPED IN THE OTHER SHEET WILL REMAIN GIVING A WRONG IDEA. confirm whether this is ok I am repeating event code and undo macro for others Private Sub Worksheet_Change(ByVal Target As Range)Dim action As String, ref As String, cfind As RangeIf Target.Column <> Range("U1").Column Then Exit SubIf Target = "" Then Exit SubApplication.enableevents = FalseOn Error GoTo errorhandleraction = TargetDebug.Print actionref = Cells(Target.Row, "R")Debug.Print refTarget.EntireRow.CopyWith Worksheets(action).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecialEnd WithIf action = "dispatched" ThenWith Worksheets("ready")Set cfind = .Columns("R:R").Cells.Find(what:=ref, lookat:=xlWhole)If Not cfind Is Nothing Thencfind.EntireRow.DeleteEnd IfEnd WithEnd IfGoTo nnexterrorhandler:MsgBox "some error check spellings"GoTo nnextnnext:MsgBox "event code over"Application.CutCopyMode = FalseApplication.enableevents = TrueEnd Sub Sub undo()Dim j As IntegerFor j = 1 To Worksheets.CountIf Worksheets(j).Name <> "List" ThenWith Worksheets(j).Range(.Range("A5"), .Cells(Rows.Count, "A")).EntireRow.DeleteEnd WithEnd IfNext jEnd Sub

venkat1926 · Answer

strictly only event codes must be parked in sheet code area
and standard macros must be parked in a module

however for convenience sake I have parked all of them in sheet code sheet


suggest following

first run undo

this clears all data except heading rows in sheets except sheet "List"
automatically some validation also will be introduced in column U IN THOSE ROWS 
where there is data in the present case rows 5 to 9 (in List)

Now you enter data in U column of sheet List taking advantage of the 
validation list
if you choose (from valaidation) e.g. Ready then relevant row will be copied in Ready sheet
in THE SAME CELL (col U in List) if you choose from validation "dispatched" then that row 
will go to Dispatched sheet and at the ame time it will check the row in ready sheet has same reference 
number (column R) and delete the row


for easy appraisal I am sendig the whole workook through speedyshare
you can download the file from this url

http://speedy.sh/8SgNJ/scully-second-version-140331-macro-enabled-Workbook-READY.xlsm

send your comments please

venkat1926 · Answer

upload YOUR file (workbook) through speedyshare.com
this workook is one workbook with two sheets and not two workooks
as I prepared the code

venkat1926 · Answer

your only problem is first you type "ready" it goes into ready sheet but when you type Dispatched (choose from validation) then it goes into Dispatched sheet but it does not remove the same row from ready sheet. that is because: there is a spelling mistake in my original macro the statement If action = "dispatched" Then the "d" in dispatched should be caps , that is If action = "Dispatched" Then for your convenience try this modified macro and let me know after modifying preferably in a duplicate file do this experiment (I did it) 1. first choose Ready from validation in row no. 13 column U. this row will be see in Rready sheet 2. COME BACK TO List sheet and enter in row o. 13 col. U (same cell as 1) choose Dispatched from validation list. now see Dispatched sheet and see the row 13 see Ready sheet it will not be there 3. now go to row 12 col U choose "Ready". it will go to Ready 4. in the same cell choose Rejected and it will go into rejected sheet but it will remain in Ready. IS THIS NOT WRONG. TELL ME . I shall change 5. if you had already chosen Ready in col u of a row and AGAIN choose Ready NATURALY this row will be duplicated in Ready sheet. IF YOU HAVE ALREADY CHOSEN Ready why should again choose Ready in the same row. This appears to be not logical clarify this I am repeating the modified macro for fullness sake Private Sub Worksheet_Change(ByVal Target As Range)Dim action As String, ref As String, cfind As RangeIf Target.Column <> Range("U1").Column Then Exit SubIf Target = "" Then Exit SubApplication.EnableEvents = FalseOn Error GoTo errorhandleraction = TargetDebug.Print actionref = Cells(Target.Row, "R")Debug.Print refTarget.EntireRow.CopyWith Worksheets(action).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecialEnd WithIf action = "Dispatched" ThenWith Worksheets("Ready")Set cfind = .Columns("R:R").Cells.Find(what:=ref, lookat:=xlWhole)If Not cfind Is Nothing Thencfind.EntireRow.DeleteEnd IfEnd WithEnd IfGoTo nnexterrorhandler:MsgBox "some error check spellings"GoTo nnextnnext:MsgBox "event code over"Application.CutCopyMode = FalseApplication.EnableEvents = TrueEnd Sub Sub undo()Dim j As IntegerFor j = 1 To Worksheets.CountIf Worksheets(j).Name <> "List" ThenWith Worksheets(j).Range(.Range("A5"), .Cells(Rows.Count, "A")).EntireRow.DeleteEnd WithEnd IfNext jEnd Sub the macro undo completely undoes the effect of event code get back to me

venkat1926 · Answer

computer is high speed idiot. it has to be told what to do. so logic is 

I am rephrasing the logic 

1. when you make first entry it will go into relevant sheet
2. when you make second entry including the same as first entry then that goes to the relevant sheet corresponding to second entry and then
in the first entry sheet it should be removed


by mistake if you do same entry more than twice. is it possible?

(see this logic applies even if second entry is same as first entry. for e.g. if first entyry is "Ready" and second entry is also "ready" , of course you cannot delete first entry in Ready sheet but it will delete one of them, 

ok one doubt. how for the computer to remember what was first entry.

I thik we can say the entry should be ONLY ONE OF THE SHEET THAT IS LATEST ENTRY SHEET. 

I ca try on this. before my doing that confirm whether my logic is ok

venkat1926 · Answer

your problem has convoluted logic. only you can judge whether event code gives what you want. I am sending the file with date line 140407.  you save this IN A NEW FOLDER SO THAT THIS IS NOT CONFUSED WITH EARLIER FILES

FIRST RUN UNDO MACRO ( for standardization this conventional macro "undo" is parked in module1 )
under developer ribbon if you click macro (second icon on the left) you will see the macro .

the event code will be in sheet1 code(that is right click sheet "List" tab and click view code you will see that.

first to clear run "uNdo ( only once in the beginning). when something goes wrong you can again run "undo" and start again 

then do experiments with sheet List adding entries. ALWAYS CHOOSE FROM VALIDATION AND NOT DIRECTLY ENTER)

make number of permutations  and then check in each case it is ok. 

if completely satisfied confirm. otherwise discussion continue

download the file from url 

http://speedy.sh/y8Ep5/scully-140407-macro-enabled-modifed.xlsm

venkat1926 · Answer

what is the version of excel you are having.if xl2007 or above 

save as 
EXCEL MACRO ENABLED WORKBOOK

if you click xl button on the top left and click save as the above choice  is the second choice
when you save like this the file will be like
.xlsm
next time on wards it enough just to 
SAVE
THAT Is CONTROL + s

when you save as EXCEL MaCRO ENABLED WORKBOOKI the macros and event codes will remain in the standard module and also in sheet code
but whenever you open you have to click "enable macros" some thing like this
on occasions when you open there is a button option at the top and if you click this 
you will be asked to check "enable macros"

does this help 

try and let me know

venkat1926 · Answer

IT DOES WORK  but there will be some  problems . the compputer (high speed idiot) does not understand you have changed reference point.  In the event code  I checked with column R WHICH IS NOT UNIQUE in the new workbook.  THIS WILL GIVE PROBLEM

I find that the col A the values are unique for each row. can I use it-column A instead of col R
this must give your idea your sample configuration sent for writing codes  must be same as original configuration. It can be extract and data may be different but configuration same


if you asree col. A is going to be unque in all your future files then I can modify the macro or event code

 second problem the data starts in row no. 10 in list where as originally it starts in row no. 9 perhaps this wont give much problem

remember one of my codes is event code and the other standard macro(undo)
event codes must be parked in the corresponding sheet code sheet and the standard macro is NORMALLY parked in standard module. where is the macro UNDO. I do not see in the new workbook.  

give your decision regarding using column A instead of column R I shall modify the event code.

venkat1926 · Answer

lot of changes are to be made in the event code. so you have to test it thoroguhly

I am sending the full file with 1)event code and 2) macro embedded.

open developer ribbon
clcik macro (second from left)
you will see two macros 1)undo and 2)formatting
do not worry about FORMATTING macro as it will be run automatically when you run macro UNDO. so it is enough ;if your run UNDO.

right click tab of sheest "lIST"  and click view code. ;you will see the event code.
event code means that this code will be fired if you there is some event. in this case if you change any value in sheet "List" AND column U (that is only U10 and down. don't mess with an thing above . this means when you change any cell from U10 down (within your data rows) the event code will be fired.

If you are likely to work frequently on excel get  good books both on excel and also excel vba for version of excel you have got. it will be useful to your profession

one more thing remember if you want to ask third party to write a macro send extract of original data. not all the data. if your data consists of more than 20 rows. 
take extract of 20 rows in another workbook and send it.

you can download the new file of yours from this url 

http://speedy.sh/hvuYP/SCULLY-LATEST-140408-Sample-Order-K.xlsm

give me feedback in course of time after thorough test

Copying the entire row using VBA

14 responses

Similar discussions

Subscribe To Our Newsletter!