Skip to main content

Conas uath-scagaire a athúsáid go huathoibríoch nuair a athraíonn sonraí in Excel?

In Excel, nuair a chuireann tú an scagairí feidhm chun sonraí a scagadh, ní athrófar toradh an scagaire go huathoibríoch leis na hathruithe sonraí i do shonraí scagtha. Mar shampla, nuair a scagaim gach Úlla ó na sonraí, anois, athraím ceann de na sonraí scagtha go BBBBBB, ach ní athrófar an toradh chomh maith leis an scáileán a thaispeántar a thaispeáint. An t-alt seo, labhróidh mé faoi conas uath-scagaire a athúsáid go huathoibríoch nuair a athraíonn sonraí in Excel.

scagaire athnuachana doc auot 1

Déan scagaire uathoibríoch a athúsáid go huathoibríoch nuair a athraíonn sonraí le cód VBA

mboilgeog cheart gorm saighead Déan scagaire uathoibríoch a athúsáid go huathoibríoch nuair a athraíonn sonraí le cód VBA

De ghnáth, is féidir leat na sonraí scagaire a athnuachan trí chliceáil ar an ngné Reapply de láimh, ach, anseo, tabharfaidh mé cód VBA isteach chun na sonraí scagaire a athnuachan go huathoibríoch nuair a athraíonn na sonraí, déan mar a leanas le do thoil:

1. Téigh go dtí an bhileog oibre ar mhaith leat scagaire a athnuachan go huathoibríoch nuair a athraíonn sonraí.

2. Cliceáil ar dheis ar an táb bileog, agus roghnaigh Féach an cód ón roghchlár comhthéacs, sa popped amach Microsoft Visual Basic d’Fheidhmchláir fuinneog, cóipeáil agus greamaigh an cód seo a leanas i bhfuinneog bán an Mhodúil, féach an scáileán:

Cód VBA: Scagaire athiarratas uathoibríoch nuair a athraíonn sonraí:

Private Sub Worksheet_Change(ByVal Target As Range)
   Sheets("Sheet3").AutoFilter.ApplyFilter
End Sub

scagaire athnuachana doc auot 2

nótaí: Sa chód thuas, Bileog 3 is ainm don bhileog le scagaire uathoibríoch a úsáideann tú, athraigh é le do riachtanas.

3. Agus ansin an fhuinneog cód seo a shábháil agus a dhúnadh, anois, nuair a athraíonn tú na sonraí scagtha, tá an scagairí déanfar an fheidhm a athnuachan go huathoibríoch ag an am céanna, féach an scáileán:

scagaire athnuachana doc auot 3

Uirlisí Táirgiúlachta Oifige is Fearr

Gnéithe Coitianta: Faigh, Aibhsigh nó Aithnigh Dúblaigh   |  Scrios Sraitheanna Bána   |  Comhcheangail Colúin nó Cealla gan Sonraí a Chailleadh   |   Babhta gan Foirmle ...
Cuardaigh Super: Ilchritéir VLookup  |   VLookup Illuachanna  |   VLookup Trasna Ilbhileoga   |   Amharc doiléir ....
Liosta anuas Casta: Go tapa Cruthaigh Liosta Anuas   |  Liosta anuas Cleithiúnach   |  Liosta Buail Isteach Ilroghnacha ....
Bainisteoir Colún: Cuir Líon Sonrach Colún leis  |  Colúin Bog  |  Scoránaigh Stádas Infheictheachta na gColún Ceilte  |  Déan comparáid idir Raonta & Colúin ...
Gnéithe Réadmhaoin: Fócas Eangaí   |  Amharc Dearaidh   |   Barra Mór na Foirmle   |  Leabhar Oibre & Bainisteoir Bileog   |  Leabharlann Acmhainní (Uaththéacs)   |  Piocálaí Dáta   |  Comhcheangail Bileoga Oibre   |  Criptigh/Díchriptigh Cealla   |  Seol Ríomhphost trí Liosta   |  Scagaire Super   |   Scagaire Speisialta (scagaire trom/iodálach/stailc tríd...) ...
Barr 15 Uirlisí:  12 Téacs uirlisí (Cuir Téacs, Bain Carachtair,...)   |   50 + Cairt cineálacha (Cairt Gantt,...)   |   40+ Praiticiúil Foirmlí (Ríomh aois bunaithe ar lá breithe,...)   |   19 Insertion uirlisí (Cuir isteach Cód QR, Ionsáigh Pictiúr ón gCosán,...)   |   12 Tiontú uirlisí (Uimhreacha le Focail, Comhshó Airgeadra,...)   |   7 Cumaisc & Scoilt uirlisí (Sraitheanna Comhcheangail Casta, Cealla Scoilt,...)   |   ... agus eile

Supercharge Do Scileanna Excel le Kutools for Excel, agus Éifeachtúlacht Taithí Cosúil Riamh Roimhe. Kutools for Excel Tairiscintí os cionn 300 Ardghné chun Táirgiúlacht a Mhéadú agus Am a Shábháil.  Cliceáil anseo chun an ghné is mó a theastaíonn uait a fháil ...

cluaisín kte 201905

Office Tab Tugann sé comhéadan Tabbed chuig Oifig, agus Déan do chuid Oibre i bhfad níos éasca

  • Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
  • Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
  • Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!
Comments (38)
Rated 5 out of 5 · 1 ratings
Larry
Larry
This comment was minimized by the moderator on the site
Its not working if able changing by itslef, like when you linking data from another app, can someone help me fix this please
Reply
0
0
John Edwards
John Edwards
This comment was minimized by the moderator on the site
Pasted it as shown, but not working. The sheet I need updated is an invoice that has formulas that are pulling from another sheet. Since the blank cells all have formulas in them, is that preventing it from working? Seems simple for it to work, so I'm getting frustrated with myself.
Reply
0
0
Sam Clarke
Sam Clarke
This comment was minimized by the moderator on the site
2022 update!
Just add this to the sheets you want it applied too.

Private Sub Worksheet_Change(ByVal Target As Range)
AutoFilter.ApplyFilter
End Sub
Reply
0
0
Rated 5 out of 5
Hetfield1985
Hetfield1985
This comment was minimized by the moderator on the site
My input data is on the first sheet of the book, and the table in which the filter is applied is on another sheet of the same book. When I change the data on the first sheet, the filter on the second sheet should be updated. But if at the time of changing the data, a cell in the range of this table is not selected, then I get an error:"Run-time error '91': Object variable or With block variable not set". Therefore, i am should always check that the cursor is in a table range cell and not in any other place on the sheet. How to fix it?Solved:
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("name of sheet").ListObjects("name of table").AutoFilter.ApplyFilter
End Sub
Reply
Report
0
0
Kay
Kay
This comment was minimized by the moderator on the site
Thank you. Works great!
Reply
0
0
Dom
Dom
This comment was minimized by the moderator on the site
Hey this works great but I'd like to re-apply the autofilter to multiple sheets if possible.
I have 3 sheets (Enquiry, Booked, No sale)

What I'm trying to do is essentially move the data when I change the item status code:
"Enquiry" is filtered to show 'In progress' only, "Booked" is filtered to show 'Booked' only etc

Is there a way to re-apply filter to multiple worksheets when making a change on "enquiry"
Reply
0
0
Danny
Danny
This comment was minimized by the moderator on the site
I actually have data from an other Excel file that got imported in a Excelsheet with the name "Database". Then I import this data in the same Excel file but in an other ExcelSheet "Overview". I want when the data changes in the orgininal source, that the filter applies in the sheet "Overview". Thank you in forward for the one who can help me :). P.S. cant use VBA in the firt excelsheet
Reply
0
0
Anthony
Anthony
This comment was minimized by the moderator on the site
Hi,

This is a great bit of code thank you. The only issue I am having is I'm using a drop down on a separate chart sheet. If I manually change the value in the cell associated with the drop down, it works. But when I try to just use the drop down, it won't update. Any thoughts?
Reply
0
0
Jetson
Jetson
This comment was minimized by the moderator on the site
Hi, thanks so much for the help. Something isn't working right for me. Here's the story.

Sheet1 has variable data. Sheet3 has static data and filter. Filter criteria on "Sheet3" comes from Sheet1. Sheet1 has data that comes from filtered results on Sheet3.

Sheet3 has code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A1:U14").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("A22:U23"), CopyToRange:=Range("A25:U26"), Unique:=False
End Sub

It works great if I do anything on Sheet3. No problems. Thank you!

At first I had code on Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Sheet3").AutoFilter.ApplyFilter
End Sub

Which resulted in the error "Runtime error 91, Object Variable or With Block not Set".

I changed the code based on comments to be:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Sheets("Sheet3").AutoFilter.ApplyFilter
End Sub

Now I don't get an error, but the data on Sheet3 and therefore Sheet1 don't change. In other words, the event of applying the filter to Sheet3 doesn't occur when I make a change on Sheet1. It doesn't matter if I hit <return> or click on another cell after changing the Sheet3 filter criteria cell that is set on Sheet1.

As an aside, I expect that if I wanted to have multiple cells on Sheet1 that caused filters on Sheets 4 and 5 in addition to Sheet3, I would need the code on Sheet 1 to read:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Sheets("Sheet3").AutoFilter.ApplyFilter
Sheets("Sheet4").AutoFilter.ApplyFilter
Sheets("Sheet5").AutoFilter.ApplyFilter
End Sub

Thanks again!
Reply
Report
0
0
Neil
Neil
This comment was minimized by the moderator on the site
Cant get this to work at all on office 365
any suggestions
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations