Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-13-2022, 06:49 AM
Eleutheros Eleutheros is offline Copy rows according to criteria to a similar table other sheet Windows 10 Copy rows according to criteria to a similar table other sheet Office 2019
Banned
Copy rows according to criteria to a similar table other sheet
 
Join Date: Feb 2022
Posts: 1
Eleutheros is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 02-13-2022, 08:23 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is online now Copy rows according to criteria to a similar table other sheet Windows 7 64bit Copy rows according to criteria to a similar table other sheet Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #3  
Old 02-13-2022, 08:27 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is online now Copy rows according to criteria to a similar table other sheet Windows 7 64bit Copy rows according to criteria to a similar table other sheet Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #4  
Old 02-13-2022, 12:07 PM
p45cal's Avatar
p45cal p45cal is online now Copy rows according to criteria to a similar table other sheet Windows 10 Copy rows according to criteria to a similar table other sheet Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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.
Reply With Quote
Reply

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 rows according to criteria to a similar table other sheet Copy Multiple Rows to new workbook when multiple criteria is met. flds Excel Programming 5 09-30-2014 09:58 AM
Copy rows according to criteria to a similar table other sheet 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
Copy rows according to criteria to a similar table other sheet Find Results in excel copy the rows to another sheet khalidfazeli Excel 2 02-06-2013 09:38 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:41 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft