Ufdjrw

Running a General Election and the European Elections together

Is wanting to ask what to write an indication that you need to change your story?

0 rank tensor vs 1D vector

Domestic-to-international connection at Orlando (MCO)

Would a grinding machine be a simple and workable propulsion system for an interplanetary spacecraft?

Can MTA send mail via a relay without being told so?

Why is my new battery behaving weirdly?

Writing differences on a blackboard

How to install OpenCV on Raspbian Stretch?

Easy to read palindrome checker

Is it ever safe to open a suspicious HTML file (e.g. email attachment)?

Does Germany produce more waste than the US?

Is micro rebar a better way to reinforce concrete than rebar?

Method for adding error messages to a dictionary given a key

A Man With a Stainless Steel Endoskeleton (like The Terminator) Fighting Cloaked Aliens Only He Can See

Is there always a complete, orthogonal set of unitary matrices?

I want to delete every two lines after 3rd lines in file contain very large number of lines :

Is there a difference between "Fahrstuhl" and "Aufzug"

Unclear about dynamic binding

Why don't programming languages automatically manage the synchronous/asynchronous problem?

Newlines in BSD sed vs gsed

Bartok - Syncopation (1): Meaning of notes in between Grand Staff

Why is quantifier elimination desirable for a given theory?

WOW air has ceased operation, can I get my tickets refunded?

Moving cells between worksheets after new calculation

0

I have found posts about how to move rows between sheets with Worksheet_Change but this of course does not help with a calculation. I cannot figure out how to use the Calculation Event properly for this. I have a worksheet for a non-profit apartment complex where the residents cannot stay for more than 5 years. I have a column that calculates how long they have been here and returns "YES" or "NO" as to whether or not they have reached the five year limit. If it returns "YES", I want their entire row to be placed in another Worksheet so that we can start the eviction process. This changes everyday so it would be most efficient if this could be automatic. Thank you for your help in advance. This is the code I have so far but it will not automatically move upon recalculation.

Private Sub Worksheet_Change(ByVal Target As Range)

 If Target.Column = 17 Then
 If UCase(Target.Value) = "YES" Then
 Target.EntireRow.Copy Destination:=Sheets("FIVE YEAR"). _
 Range("A" & Rows.Count).End(xlUp).Offset(1)
 End If
 End If

 If Target.Column = 22 Then
 If UCase(Target.Value) = "YES" Then
 Target.EntireRow.Copy Destination:=Sheets("PAST RESIDENTS"). _
 Range("A" & Rows.Count).End(xlUp).Offset(1)
 Target.EntireRow.Delete
 End If
 End If

End Sub

excel vba excel-vba

share|improve this question

edited Mar 7 at 16:40

Cœur

19.1k9114155

asked Jun 9 '15 at 12:44

Andrew LachawiecAndrew Lachawiec

11

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  You could use the Worksheet_Open event with Application.CalculateFullRebuild at the start to ensure everything is up to date. Then you don't have to worry editing and deleting rows inside a Worksheet_Change or Worksheet_Calculate event. Or add a button to the sheet and let people click it to do the processing. I'm never a fan of putting code that modifies the workbook heavily inside events. That's a personal preference though, people do it all the time. Also not a fan of evicting folks, but that's not an Excel issue.
  
  – Byron Wall
  Jun 9 '15 at 14:25
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yea I thought about the button idea but I believe that would make duplicate entries into the second worksheet every time you clicked it because I do not want to delete the residents who have been in the first sheet. I am also not a fan of eviction but as a non-profit organization meant to help people get established in the city, you need to make room for the others who need help.
  
  – Andrew Lachawiec
  Jun 10 '15 at 14:52
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Duplicates could be addressed if there is a unique ID and check for it (using Application.Match or .Find). If you have no duplicates normally, you can just run Data->RemoveDuplicates after the processing and it will clean things up. There is a VBA version of that as well so it could be automated. Ideally, you would check first, but post-processing is common also.
  
  – Byron Wall
  Jun 10 '15 at 15:07
  

  
  
  
  

add a comment | 

0

I have found posts about how to move rows between sheets with Worksheet_Change but this of course does not help with a calculation. I cannot figure out how to use the Calculation Event properly for this. I have a worksheet for a non-profit apartment complex where the residents cannot stay for more than 5 years. I have a column that calculates how long they have been here and returns "YES" or "NO" as to whether or not they have reached the five year limit. If it returns "YES", I want their entire row to be placed in another Worksheet so that we can start the eviction process. This changes everyday so it would be most efficient if this could be automatic. Thank you for your help in advance. This is the code I have so far but it will not automatically move upon recalculation.

Private Sub Worksheet_Change(ByVal Target As Range)

 If Target.Column = 17 Then
 If UCase(Target.Value) = "YES" Then
 Target.EntireRow.Copy Destination:=Sheets("FIVE YEAR"). _
 Range("A" & Rows.Count).End(xlUp).Offset(1)
 End If
 End If

 If Target.Column = 22 Then
 If UCase(Target.Value) = "YES" Then
 Target.EntireRow.Copy Destination:=Sheets("PAST RESIDENTS"). _
 Range("A" & Rows.Count).End(xlUp).Offset(1)
 Target.EntireRow.Delete
 End If
 End If

End Sub

excel vba excel-vba

share|improve this question

edited Mar 7 at 16:40

Cœur

19.1k9114155

asked Jun 9 '15 at 12:44

Andrew LachawiecAndrew Lachawiec

11

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  You could use the Worksheet_Open event with Application.CalculateFullRebuild at the start to ensure everything is up to date. Then you don't have to worry editing and deleting rows inside a Worksheet_Change or Worksheet_Calculate event. Or add a button to the sheet and let people click it to do the processing. I'm never a fan of putting code that modifies the workbook heavily inside events. That's a personal preference though, people do it all the time. Also not a fan of evicting folks, but that's not an Excel issue.
  
  – Byron Wall
  Jun 9 '15 at 14:25
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yea I thought about the button idea but I believe that would make duplicate entries into the second worksheet every time you clicked it because I do not want to delete the residents who have been in the first sheet. I am also not a fan of eviction but as a non-profit organization meant to help people get established in the city, you need to make room for the others who need help.
  
  – Andrew Lachawiec
  Jun 10 '15 at 14:52
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Duplicates could be addressed if there is a unique ID and check for it (using Application.Match or .Find). If you have no duplicates normally, you can just run Data->RemoveDuplicates after the processing and it will clean things up. There is a VBA version of that as well so it could be automated. Ideally, you would check first, but post-processing is common also.
  
  – Byron Wall
  Jun 10 '15 at 15:07
  

  
  
  
  

add a comment | 

I have found posts about how to move rows between sheets with Worksheet_Change but this of course does not help with a calculation. I cannot figure out how to use the Calculation Event properly for this. I have a worksheet for a non-profit apartment complex where the residents cannot stay for more than 5 years. I have a column that calculates how long they have been here and returns "YES" or "NO" as to whether or not they have reached the five year limit. If it returns "YES", I want their entire row to be placed in another Worksheet so that we can start the eviction process. This changes everyday so it would be most efficient if this could be automatic. Thank you for your help in advance. This is the code I have so far but it will not automatically move upon recalculation.

Private Sub Worksheet_Change(ByVal Target As Range)

 If Target.Column = 17 Then
 If UCase(Target.Value) = "YES" Then
 Target.EntireRow.Copy Destination:=Sheets("FIVE YEAR"). _
 Range("A" & Rows.Count).End(xlUp).Offset(1)
 End If
 End If

 If Target.Column = 22 Then
 If UCase(Target.Value) = "YES" Then
 Target.EntireRow.Copy Destination:=Sheets("PAST RESIDENTS"). _
 Range("A" & Rows.Count).End(xlUp).Offset(1)
 Target.EntireRow.Delete
 End If
 End If

End Sub

excel vba excel-vba

share|improve this question

edited Mar 7 at 16:40

Cœur

19.1k9114155

asked Jun 9 '15 at 12:44

Andrew LachawiecAndrew Lachawiec

11

Private Sub Worksheet_Change(ByVal Target As Range)

 If Target.Column = 17 Then
 If UCase(Target.Value) = "YES" Then
 Target.EntireRow.Copy Destination:=Sheets("FIVE YEAR"). _
 Range("A" & Rows.Count).End(xlUp).Offset(1)
 End If
 End If

 If Target.Column = 22 Then
 If UCase(Target.Value) = "YES" Then
 Target.EntireRow.Copy Destination:=Sheets("PAST RESIDENTS"). _
 Range("A" & Rows.Count).End(xlUp).Offset(1)
 Target.EntireRow.Delete
 End If
 End If

End Sub

share|improve this question

edited Mar 7 at 16:40

Cœur

19.1k9114155

asked Jun 9 '15 at 12:44

Andrew LachawiecAndrew Lachawiec

11

share|improve this question

edited Mar 7 at 16:40

Cœur

19.1k9114155

asked Jun 9 '15 at 12:44

Andrew LachawiecAndrew Lachawiec

11

edited Mar 7 at 16:40

Cœur

19.1k9114155

edited Mar 7 at 16:40

Cœur

19.1k9114155

asked Jun 9 '15 at 12:44

Andrew LachawiecAndrew Lachawiec

11

asked Jun 9 '15 at 12:44

Andrew LachawiecAndrew Lachawiec

11