Moving cells between worksheets after new calculation The Next CEO of Stack OverflowLoop through a range of cells, inputting data into other cells (drop-down selections) and return the result of calculationsMove Row to another worksheet where cell equals worksheet nameFIXED: Reference an Excel Sheet Name with a variableFixing my macro to copy a range to the next blank column?VBA Query Table will not pull data into worksheet after using string for htmlInterior color of cells up to the last cell with data in a columnConditional copy Excel File-2 data to excel file-1?Take the date in one worksheet and find the same date in another worksheet column and return the cell reference for that date to use in a loopExcel VBA Copy Destination syntax with variables for Workbook/Worksheettrouble declaring a variable as a specific cell on a worksheet

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



The Next CEO of Stack OverflowLoop through a range of cells, inputting data into other cells (drop-down selections) and return the result of calculationsMove Row to another worksheet where cell equals worksheet nameFIXED: Reference an Excel Sheet Name with a variableFixing my macro to copy a range to the next blank column?VBA Query Table will not pull data into worksheet after using string for htmlInterior color of cells up to the last cell with data in a columnConditional copy Excel File-2 data to excel file-1?Take the date in one worksheet and find the same date in another worksheet column and return the cell reference for that date to use in a loopExcel VBA Copy Destination syntax with variables for Workbook/Worksheettrouble declaring a variable as a specific cell on a worksheet










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



















  • 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















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



















  • 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













0












0








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
















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




excel vba excel-vba






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Mar 7 at 16:40









Cœur

19.1k9114155




19.1k9114155










asked Jun 9 '15 at 12:44









Andrew LachawiecAndrew Lachawiec

11




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












  • 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







1




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





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





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





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












0






active

oldest

votes












Your Answer






StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);













draft saved

draft discarded


















StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f30732400%2fmoving-cells-between-worksheets-after-new-calculation%23new-answer', 'question_page');

);

Post as a guest















Required, but never shown

























0






active

oldest

votes








0






active

oldest

votes









active

oldest

votes






active

oldest

votes















draft saved

draft discarded
















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid …


  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f30732400%2fmoving-cells-between-worksheets-after-new-calculation%23new-answer', 'question_page');

);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







-

Popular posts from this blog

Save data to MySQL database using ExtJS and PHP [closed]2019 Community Moderator ElectionHow can I prevent SQL injection in PHP?Which MySQL data type to use for storing boolean valuesPHP: Delete an element from an arrayHow do I connect to a MySQL Database in Python?Should I use the datetime or timestamp data type in MySQL?How to get a list of MySQL user accountsHow Do You Parse and Process HTML/XML in PHP?Reference — What does this symbol mean in PHP?How does PHP 'foreach' actually work?Why shouldn't I use mysql_* functions in PHP?

Image
Does this AnyDice function accurately calculate the number of ogres you make unconcious with three 4th-level castings of Sleep? Making a sword in the stone, in a medieval world without magic Identifying the interval from A♭ to D♯ Why is Vishnu's skin colour mentioned as both white and blue in the Vishnu-sahasranaama? SQL Server Primary Login Restrictions Have researchers managed to "reverse time"? If so, what does that mean for physics? Why does Deadpool say "You're welcome, Canada," after shooting Ryan Reynolds in the end credits? Check this translation of Amores 1.3.26 Why did it take so long to abandon sail after steamships were demonstrated? Plot a function of two variables equal 0 2D counterpart of std::array in C++17 Could the Saturn V actually have launched astronauts around Venus? Why is "das Weib" grammatically neuter? How to make healing in an exploration game interesting Life insurance that covers only simulta
Read more

Compiling GNU Global with universal-ctags support Announcing the arrival of Valued Associate #679: Cesar Manara Planned maintenance scheduled April 23, 2019 at 23:30 UTC (7:30pm US/Eastern) Data science time! April 2019 and salary with experience The Ask Question Wizard is Live!Tags for Emacs: Relationship between etags, ebrowse, cscope, GNU Global and exuberant ctagsVim and Ctags tips and trickscscope or ctags why choose one over the other?scons and ctagsctags cannot open option file “.ctags”Adding tag scopes in universal-ctagsShould I use Universal-ctags?Universal ctags on WindowsHow do I install GNU Global with universal ctags support using Homebrew?Universal ctags with emacsHow to highlight ctags generated by Universal Ctags in Vim?

Image
How to ask rejected full-time candidates to apply to teach individual courses? Why these surprising proportionalities of integrals involving odd zeta values? A German immigrant ancestor has a "Registration Affidavit of Alien Enemy" on file. What does that mean exactly? Why did Israel vote against lifting the American embargo on Cuba? Unix AIX passing variable and arguments to expect and spawn Kepler's 3rd law: ratios don't fit data Why does my GNOME settings mention "Moto C Plus"? Continue tikz picture on next page What documents does someone with a long-term visa need to travel to another Schengen country? “Since the train was delayed for more than an hour, passengers were given a full refund.” – Why is there no article before “passengers”? How to create a command for the "strange m" symbol in latex? How to get a single big right brace? Is the Mordenkainen's Sword spell underpowered? Why did Bronn offer to be Tyrion L
Read more

Add ONERROR event to image from jsp tldHow to add an image to a JPanel?Saving image from PHP URLHTML img scalingCheck if an image is loaded (no errors) with jQueryHow to force an <img> to take up width, even if the image is not loadedHow do I populate hidden form field with a value set in Spring ControllerStyling Raw elements Generated from JSP tagds with Jquery MobileLimit resizing of images with explicitly set width and height attributeserror TLD use in a jsp fileJsp tld files cannot be resolved

Image
At which point does a character regain all their Hit Dice? Why are on-board computers allowed to change controls without notifying the pilots? How do I keep an essay about "feeling flat" from feeling flat? Time travel short story where a man arrives in the late 19th century in a time machine and then sends the machine back into the past How to be diplomatic in refusing to write code that breaches the privacy of our users Understanding "audieritis" in Psalm 94 Greatest common substring Go Pregnant or Go Home Why did Kant, Hegel, and Adorno leave some words and phrases in the Greek alphabet? What to do with wrong results in talks? Failed to fetch jessie backports repository Increase performance creating Mandelbrot set in python How can I replace every global instance of "x[2]" with "x_2" What are the ramifications of creating a homebrew world without an Astral Plane? Are there any comparative studies done between Ashtava
Read more