Forum Discussion

Brittany0724's avatar
Brittany0724
Copper Contributor
Oct 30, 2020

Moving Rows Based on Information in a Cell

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.

excel
Macros and VBA

12 Replies

    • mtarler's avatar
      mtarler
      Silver Contributor
      Nov 09, 2020

      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.   

  • mtarler's avatar
    mtarler
    Silver Contributor
    Oct 30, 2020

    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's avatar
    NikolinoDE
    Platinum Contributor
    Oct 30, 2020

    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's avatar
      Brittany0724
      Copper Contributor
      Nov 05, 2020

      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?

Resources