Copying the entire row using VBA [Closed]

Report
Posts
2
Registration date
Wednesday March 12, 2014
Status
Member
Last seen
March 13, 2014
-
 Blocked Profile -
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.

14 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
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, x
Dim j As Integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False


With Worksheets("sheet1")
Range(.Range("A1").End(xlDown).Offset(1, 0), .Cells(Rows.Count, "A")).EntireRow.Delete
Set r6 = Range(.Range("F1"), .Range("F1").End(xlDown))
Set filt = .Range("A1").End(xlDown).Offset(5, 0)
Set rdata = .Range("A1").CurrentRegion
r6.AdvancedFilter xlFilterCopy, , filt, True
Set filt = Range(filt.Offset(1, 0), filt.End(xlDown))
For Each cfilt In filt
x = cfilt
rdata.AutoFilter field:=6, Criteria1:=x
rdata.Offset(1, 0).Resize(rdata.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy
On Error Resume Next

If Exists(Worksheets(x)) Then
GoTo nextstep
Else
Worksheets.Add
ActiveSheet.Name = x
End If
nextstep:
With Worksheets(x)
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
.AutoFilterMode = False
Next cfilt
With Worksheets("sheet1")
Range(.Range("A1").End(xlDown).Offset(1, 0), .Cells(Rows.Count, "A")).EntireRow.Delete
End With

MsgBox "macro done"
Application.ScreenUpdating = True
Application.CutCopyMode = False
Application.DisplayAlerts = True
End With

End Sub


Function Exists(sh As Worksheet)
Dim ws As Worksheet
On Error Resume Next
Set ws = sh
If Err.Number <> 0 Then
Exists = False
Else
Exists = True
End If
End Function


Sub undo()
Application.DisplayAlerts = False
Dim j As Integer
For j = Worksheets.Count To 1 Step -1
If Worksheets(j).Name <> "Sheet1" Then Worksheets(j).Delete
Next j
With Worksheets("sheet1")
Range(.Range("A1").End(xlDown).Offset(1, 0), .Cells(Rows.Count, "A")).EntireRow.Delete
End With
End Sub
Posts
2
Registration date
Wednesday March 12, 2014
Status
Member
Last seen
March 13, 2014

Hi, thank you for the code....
I'am not very much familiar with VBA, but I have copy and paste this code to the "View Code" of the sheet but nothing happened. Can you please tell me what am I missing out?
Thanks in advance!
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
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
Hello!
I did the step as per your instruction, but this message pop up on my screen "Run-time error "9":
Subscript out of range
End Debug Help (These are the options)

Also, can you please let me know which option should I choose (in my Macro setting) to enable this code to run?

Hope to get your reply soon. Thank you!
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
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.
Hi, this is the url http://speedy.sh/qC7c2/Workbook.xlsx
Hope it won't cause you too much trouble as I have moved the "Status" column to column T.
Also, I have made changes on the categories Production, Dispatched, Ready, Rejected and Hold Cancelled.

Is it possible that if I changed the status, the row will be deleted from the it's previous status and will be copied to the new category.
For example, if the order is "Ready" and I put in the Status "Ready" that line will be copied on the Ready sheet. If the Order was approved, then I shall change it's status to "Dispatched". Once I changed it, the info in the Ready sheet will be deleted and will be copied to the Dispatched sheet.

Hope you get the idea.

Thank you in advance and I will wait for your reply.

Regards!
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
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?
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!
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
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 Range

If Target.Column <> Range("U1").Column Then Exit Sub
If Target = "" Then Exit Sub
Application.enableevents = False
On Error GoTo errorhandler

action = Target
Debug.Print action
ref = Cells(Target.Row, "R")
Debug.Print ref
Target.EntireRow.Copy
With Worksheets(action)
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
If action = "dispatched" Then
With Worksheets("ready")
Set cfind = .Columns("R:R").Cells.Find(what:=ref, lookat:=xlWhole)
If Not cfind Is Nothing Then
cfind.EntireRow.Delete
End If

End With
End If
GoTo nnext
errorhandler:
MsgBox "some error check spellings"
GoTo nnext
nnext:
MsgBox "event code over"
Application.CutCopyMode = False
Application.enableevents = True
End Sub


Sub undo()
Dim j As Integer
For j = 1 To Worksheets.Count
If Worksheets(j).Name <> "List" Then
With Worksheets(j)
.Range(.Range("A5"), .Cells(Rows.Count, "A")).EntireRow.Delete
End With
End If
Next j

End Sub
Hi venkat1926,

Thank you working on this

Got a few questions...

Note that the copying part is working perfectly, but the deletion of the row is not happening?
When I put "ready" on col U, the row is copied in the Ready sheet. When I changed it to "Dispatched" the row is copied on the Dispatched sheet but the same row is not deleted in the Ready sheet... It's still they. I tried a couple of sample and it's the same. The row are not being deleted...

Is there any way to avoid this?
"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 "
I understand your point, is there any way to avoid this? Maybe a code to reject the row if the same order number already exist in the same sheet?....

Lastly, is it possible to just put a dropdown categories of the col U? So the column U will have a standard status.... to above misspelling or encoding w whole new diff kind of status?

I'm very grateful for your assistance.

As usual, I will wait for your kind reply. Thank you!

Regards!
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
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
Hello venkat1926,

I have downloaded the test the it by taking a status from the drop down list. But nothing is happening.
No row is being copied in any of the sheets (Production / Dispatched / Ready).

What I did is I copied the code from the latest file that you have sent to my workbook.
It worked but it has the same problems...
1)List Sheet - When I selected "ready" from the drop down list in col U, the entire row is copied in the Ready Sheet. Then I change the status to "dispatched", the same row is copied in the Dispatched Sheet but the same row remains in the Ready Sheet. It is not deleted.

2)List Sheet - When I selected "ready" from the drop down list in Col U, the entire row is copied in the Ready Sheet. And then on the same row, I selected "ready" again in the drop down list in Col U, the the same row is again copied in the Ready Sheet.
Maybe you can put a code, that the same row will not be copied if it has the same Order Nr. (Col A)?

Thank you in advance!

Regards,
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
upload YOUR file (workbook) through speedyshare.com
this workook is one workbook with two sheets and not two workooks
as I prepared the code
Hi!

This is the url
http://speedy.sh/YMu8b/Workbook1.xlsx


Please note that I have tried to again various scenarios...
and it's all the same, First I put the first status, then the row is copied on the corresponding Sheet as per the Status. Then when I change the status, the same row is is copied on the new Sheet as per the New Status but the row remains in the sheet as per the old status.

Ex. "Production" status, the entire row is copied in the Production Sheet, but when changed to Dispatched, the row is copied in the Dispatched Sheet but the same row remains in the Production Sheet....

This is the code i'am using on my workbook.

Option Explicit
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Dim action As String, ref As String, cfind As Range

If Target.Column <> Range("U1").Column Or Target.Value = "" Then Exit Sub
Application.enableevents = False
On Error GoTo errorhandler

action = Target
Debug.Print action
ref = Cells(Target.Row, "R")
Debug.Print ref
Target.EntireRow.Copy
With Worksheets(action)
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
If action = "Dispatched" Then
With Worksheets("ready")
Set cfind = .Columns("R:R").Cells.Find(what:=ref, lookat:=xlWhole)
If Not cfind Is Nothing Then
cfind.EntireRow.Delete
End If

End With
End If
GoTo nnext
errorhandler:
MsgBox "some error check spellings"
GoTo nnext
nnext:
MsgBox "event code over"
Application.CutCopyMode = False
Application.enableevents = True
End Sub

Sub undo()
Dim j As Integer
Application.enableevents = False
For j = 1 To Worksheets.Count
If Worksheets(j).Name <> "List" Then
With Worksheets(j)
.Range(.Range("A5"), .Cells(Rows.Count, "A")).EntireRow.Delete
End With
End If
Next j
With Worksheets("List")
Range(.Range("U5"), .Cells(Rows.Count, "U")).Cells.Clear
.Range("Z1").EntireColumn.Delete
End With
validation
Application.enableevents = True
End Sub


Sub validation() 'DO THIS ONLY ONCE AT THE BEGINNING UNLESS YOU EZTEND DATA TO MORE ROWS
'IF YOU EXTEND ORIGINAL DATA WITH MORE ROWS AGAIN RUN THIS MACRO
Dim j As Integer, row1 As Integer, rowend As Integer
Application.enableevents = False
For j = 1 To Worksheets.Count
If Worksheets(j).Name <> "List" Then
With Worksheets("List")
.Range("Z4").Offset(j, 0) = Worksheets(j).Name
End With
End If
Next j

With Worksheets("List")
row1 = .Range("A5").Row
rowend = .Range("A5").End(xlDown).Row

With Range(.Cells(row1, "U"), .Cells(rowend, "U")).validation
'Range(.Cells(row1, "U"), .Cells(rowend, "U")).validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=$Z$6:$Z$10"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End With
Application.enableevents = True
End Sub


Thank you and regards!
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
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 Range

If Target.Column <> Range("U1").Column Then Exit Sub
If Target = "" Then Exit Sub
Application.EnableEvents = False
On Error GoTo errorhandler

action = Target
Debug.Print action
ref = Cells(Target.Row, "R")
Debug.Print ref
Target.EntireRow.Copy
With Worksheets(action)
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
If action = "Dispatched" Then
With Worksheets("Ready")
Set cfind = .Columns("R:R").Cells.Find(what:=ref, lookat:=xlWhole)
If Not cfind Is Nothing Then
cfind.EntireRow.Delete
End If

End With
End If
GoTo nnext
errorhandler:
MsgBox "some error check spellings"
GoTo nnext
nnext:
MsgBox "event code over"
Application.CutCopyMode = False
Application.EnableEvents = True
End Sub

Sub undo()
Dim j As Integer
For j = 1 To Worksheets.Count
If Worksheets(j).Name <> "List" Then
With Worksheets(j)
.Range(.Range("A5"), .Cells(Rows.Count, "A")).EntireRow.Delete
End With
End If
Next j

End Sub


the macro undo completely undoes the effect of event code

get back to me
Hi!

I've test the macro.
For the first test, the macro is working great!
This is when I choose ready from the validation in row 13 column U. The row was copied in the Ready sheet. Then when I change the status in the col 13 in the List Sheet to Dispatched, the row was copied in the Dispatched sheet and deleted from the Ready Sheet.
This exercise if fine.

But, the rest should be working the same way.
For example, first we select Hold Cancelled, then it should be copied to the Hold Cancelled sheet, but once we change the status to Ready, then the row should be deleted from the Hold Sheet and they copied to the Ready Sheet.
All the rows in the List Sheet should be copied on the other sheet (Production, Dispatched, Ready, Rejected, Hold Cancelled) one at a time only. Meaning, one row cannot be in the Dispatched Sheet while also appearing in the Production sheet or in the Hold Cancelled Sheet and at the same time also in the Ready Sheet.
So, once the status of the row (col U) is changed, the row in the previous sheet must be automatically deleted but copied to the new sheet at per the new status.


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
the first choose Ready from validation in row no. 13 column U. this row will be see in Rready sheet
--- this is WRONG as I have explained above. The row should be deleted from the Rejected Sheet and then copied to the Ready sheet.
This should be applicable to all the event, whether from Hold Cancelled to Dispatched, Production to Ready, Dispatched to Rejected and so on.

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
---Yes it is really not logical, but sometimes we cannot avoid this from happening.
So, to avoid duplicating the same row on the same sheet, would it be possible to put a code that will check if the Order No. (Col A) is already existing in the sheet. If the Order No. (Col A) is already in the sheet, then the row will no longer be copied.

Hope that the above is clear.

Thanks in advance!

Regards,
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
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
Hello!

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
----This is absolutely correct!

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.
----Yes, we can work with this also.
(Please ensure that there will only be one row on the sheet for each Order No.)

Thank you and hope to receive you reply soon.

Regards,
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
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
Hi,

I've does various tests and I'm so glad to say that it is working perfectly well as per my requirement.
THANK YOU SO MUCH!!!


Hope you don't mind if I ask your assistance on saving the macro.
Currently what I am doing is copying the macro and then paste it to my workbook (View Code).
But I have to do this each time I re-open the workbook (Because the macro is gone when I re-open the workbook)
Could you please tell me what should I do order for the workbook to always have the macro.

I'm using Windows 7 and my Microsoft Office is 2010.

Thank you in advance!


Regards,
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
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
Hi venkat1926,

Great! Was able to save the macro enabled workbook. Thanks!
Not so good news though....
You know that the workbooks that we are using is just sample of the actual workbook. So after testing the macro of our sample worksheet (and it's working ok) what I did is I copied the macro on my actual workbook. Sadly, it does not work.

Could you please check why is that so....
below is the link where you can download the part of the actual workbook.
http://speedy.sh/TUfmR/Sample-Order-K.xlsm

Hope you can find out the mistake I am doing.

Thank you in advance!

Regards,
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
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.
Hi!

Yes, please proceed with replacing Column R with Column A.

Thank you!

Regards,
Hi again!

I think I'm putting all the entire code as event code....

"where is the macro UNDO. I do not see in the new workbook"
----this is my bad... All this time I thought its only one code. As I don't know which one is the event code and which one is the standard macro....
Could you please park the codes to the correct place on my latest workbook.

Thank you.

Reagrds,
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
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
Hi,

I have tested the workbook with macro and I am glad to say that it is now working brilliantly! Good job! :)
I am gonna use the file and hope to get a positive response from my colleague and supervisor.

Thank you so much for your assistance and hard work.
I will surely get myself updated with excel and excel vba as I realize its good to have good knowledge on this.

Again thank you very much!

Regards,
Blocked Profile
Whew, I hope your boss and peers like venkats work! Great Job!