Moving Rows Based on Information in a Cell

Copper Contributor

I am trying to create a Macro that when ran it will move certain rows depending on the value in the cells in a specific column. I have a spreadsheet that I need to split into two sheets depending upon the card numbers that are in one of the columns. Could someone help me with what the Macro for this would look like?

 

Thank you.

12 Replies

@Brittany0724 

  Here is a small Solution approach with VBA

 

   Private Sub Worksheet_Change (ByVal Target As Range)
     column = Target.Column
     line = Target.Row
     value = Target.Value
     If column = 11 Then
         Cells (line, value) .Value = Cells (line, 1)
     End If
     End Sub

 

 

Hope I was able to help you and would be happy to know if I could help you.

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

@Brittany0724  alternatively you can leave the original sheet and on the other 2 sheets just use the FILTER() function or a pivot table to populate that sheet with the corresponding data.  If you need help doing that you could supply a sample workbook (no personal/confidential info) and we could show you more specific answer(s). but in general a FILTER() would look like:

=FILTER(Sheet1!A:M,Sheet1!A:A="only this id","None Found")

where "only this id" would be a number or text that should be matched for that row to be included and A:A be changed to the column of interest. 

@NikolinoDE I think something like this is what I am looking for but maybe if I upload a sample file it will help some to narrow down what it would look like for me. I want to be able to run a macro that will split the list into two lists depending upon values in column B.

 

I have attached a sample spreadsheet to help explain what I'm referring to. In the sample, values "****00022", "****00031", and "****00141" in column B would correspond with one list and values "****00131", "****00231", and "****00201" in column B would correspond with a second list. What is the best way to approach splitting the big list into two lists using VBA?

@Brittany0724 

Please explain to me what exactly you would like, which data or content should be moved from which cell to which?

 

Enclosed some information.

How to select cells/ranges by using Visual Basic procedures in Excel

https://docs.microsoft.com/en-us/previous-versions/office/troubleshoot/office-developer/select-cells...

 

Excuse me for the inconvenience, but need precise information in order to be able to provide you with an acceptable solution.

 

Thank you for your understanding and patience

 

Nikolino

I know I don't know anything (Socrates)

@NikolinoDE  No inconvenience at all. I appreciate your help. 

 

I would like it to split the list into two depending on what information is in the "Card number" Column (B). I need the other two columns for that row to move with it as well. 

 

To explain in more detail, if the "card number" (column B) is ****00022, ****00131, and ****00231 those cells and the cells in the same rows as those (the cells in column A & C) all need moved into a new sheet. The remaining "card numbers" and corresponding cells in the same rows can stay on the first sheet.

 

Hopefully this makes more sense. If I need to clarify more, please let me know.

@Brittany0724 

Sry but now I'm totally confused. Could you upload a file (like the one before) where you could show me how to get the data and how it should be ... with a little example.

 

Thank you for your understanding and patience

 

Nikolino

I know I don't know anything (Socrates)

@NikolinoDE 

I have attached a file to help better explain. The first sheet is the original list. I have highlighted the cells that will be used to determine which entries will go to which list. The other two sheet are what I need to end up with. Row one will always be the same on the original list and the final lists but the other rows will change depending how what values are in Column B. For example when looking at the first sheet, one week there may be more rows with "****00022" and other weeks there may be more rows with "****00231". This is why I need to do it based on the value in column B. Please let me know if there is something that I need to explain better.

 

Thank you for your help.

@Brittany0724  As I mentioned before, you can do this withOUT a macro.  Is there a reason you NEED a macro?  In the attached I put a formula into your sheet1 (and sheet2) to create those lists:

 

=FILTER(report!A:C,(RIGHT(report!$B:$B,5)=$D$1)+(RIGHT(report!$B:$B,5)=$E$1)+(RIGHT(report!$B:$B,5)=$F$1),"none")

 

For your convenience I used cells D1,E1, and F1 to define the text that should be included on that sheet.  You can easily expand on this for more options or criteria.  If you have a lot of criteria we could make it search a whole array of options but I figured for only a few options this is easier to read/understand. See attached.

@Brittany0724 

I'm not the "big pro" when it comes to pivot

(to put it mildly, my knowledge is modest, very modest :).

But I see in your plan that this can be the best way.

I will also work out a VBA macro, but first I would like to ask if this is what you want to express in the end the result?

 

I would appreciate your feedback.

 

Thank you for your understanding and patience

 

Nikolino

I know I don't know anything (Socrates)

 

@Brittany0724 

Hello, here is an additional approach with VBA that I found on the Internet.

You can customize it according to your wish.

 

Hope this will help you too.

 

Thanks for your time and patience

 

Cheers

Nikolino

I know I don't know anything (Socrates)

@NikolinoDE  I was going to mention pivot table option but I am also no expert on them and find them annoying to set all the properties to get them to look the way I want and because in my experiences I have had issues with them.  That said, I did take your lead and include in the attached, pivot tables on sheets 1 and 2 that basically replicate the table that @Brittany0724  has on each of those pages. (Note: I have Card Number listed first but that can be changed back to column 2)  

On sheet 1 I used the drop down menu to manually filter but the items desired and on sheet 2 I added a slicer to do the filtering.

@NikolinoDE If you are going to continue with a Macro solution, you will still need to have some input/strategy for identifying what card numbers need to  filter by and a way to 'run' the macro.  As a "cute" alternative you could write a macro that pastes the FILTER() formula I used above and then copy and paste values instead of actually writing the code to perform the individual selections.

 

@NikolinoDE  Hello my friend.  I was curious and looked at the macro file you just sent and I don't read German but from what I can work out based on the algorithm of what the macro is doing I believe it is doing the opposite of what the original poster wanted.  It appears to take a workbook with many sheets in it, in this case A-01, B-02, C-03....  each with date and then MERGING all those individual sheets of data into 1 master table.