#1
|
|||
|
|||
Copy rows according to criteria to a similar table other sheet
Hello, everyone
I have a database Technical Incidents reported by third party as shown in my attached file I need to do an autofilter that filters the "In Progress" and "Closed" data and let Show only "In Progress" Incidents (Excel VBA) the "Closed" data must be systematically copied from the "Incidents" sheet to the "Archive" sheet which contains a similar table as the incident is "Closed" thank you for your help I tried this code is it bug Private Sub Worksheet_Change(ByVal target As Range) If target <> "" And target.Column = 10 Then Call copie(target): Exit Sub Set isct = Intersect(target, Range("E:E")) If Not isct Is Nothing Then Call madate(isct) End Sub Sub copie(valeur) Application.EnableEvents = False If valeur <> "" And valeur.Column = 10 Then With valeur.Parent.ListObjects("BASE_INCIDENTS") Set zone = .ListRows(valeur.Row - .HeaderRowRange.Row).Range End With With Sheets("Archive").ListObjects("Tableau2") Set l = .ListRows.Add zone.Copy l.Range End With zone.Delete End If Application.EnableEvents = True End Sub Sub madate(isct) Application.EnableEvents = False For Each d In isct.Cells If IsEmpty(d) Then d.Offset(0, -3) = "" Else d.Offset(0, -3) = Format(Now, "mm/dd/yy") End If Next For Each h In isct.Cells If IsEmpty(h) Then h.Offset(0, -2) = "" Else h.Offset(0, -2) = Format(Now, "hh:mm:ss") End If Next Application.EnableEvents = True End Sub 1644747465429.png Classeur_Incidents.xlsm Classeur_Incidents.xlsm |
#2
|
||||
|
||||
There is a sub- forum dedicated to VBA questions...Ask a mod to move your thread. Thx
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
||||
|
||||
Cross posted at Copy rows according to criteria to a similar table other sheet
and Copy rows according to criteria to a similar table other sheet | MrExcel Message Board
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#4
|
||||
|
||||
This may not help; I was getting an error on this line:
If target <> "" And target.Column = 10 Then Call copie(target): Exit Sub However I was exploring column D which had the function =LastAuthor() and this was showing as #NAME? So when the code reached If target <> "" while target was an error it caused the whole line to error. 2 things: 1. change that line to: If target.Column = 10 Then If target <> "" Then Call copie(target): Exit Sub This separates the two tests, so there's less likely to be an error generated in column 10 (no formula). 2. Why is there an error in cells with =LastAuthor() ? It's because you've named a module LastAuthor as well as a function LastAuthor. Change the module name to say LastAuthor_ then there won't be a clash. (You don't need to have individual UDFs in separate modules, you can put them all together in one. As an aside, the line in Sub copie(valeur): zone.Copy l.Range copies formulae too, so there's a danger that a formula such as =ListAuthor() will recalculate when another user comes along. Consider doing something along the lines of: l.Range.value = zone.Value This should copy plain values over which won't change when other users open the workbook. |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Copy data from one sheet to another based on a certain criteria | shina67 | Excel Programming | 2 | 12-28-2016 07:32 AM |
Want to return a value from my pricing table into my raw data sheet given criteria in the data sheet | mcronin | Excel | 1 | 05-09-2016 09:43 AM |
Copy Multiple Rows to new workbook when multiple criteria is met. | flds | Excel Programming | 5 | 09-30-2014 09:58 AM |
Is it possible to copy non-contiguous rows of a Table and paste them as a separate Table in Word? | Joey Cheung | Word Tables | 1 | 08-12-2014 05:15 PM |
Find Results in excel copy the rows to another sheet | khalidfazeli | Excel | 2 | 02-06-2013 09:38 AM |