Move an entire row to another sheet based on cell value

M

morvotron

New member
Joined
Sep 19, 2017
Messages
2
Reaction score
0
Points
0
Hello everybody, i'm new here and i'm aiming to learn new stuff :)

So far, i've come with a problem i hope you can help me with, it would be great.

I have an excel file with a couple of columns with contacts. I need to split this contacts into sheets depending on the language they speak. I created a sheet for each language and i want to move, for example, like this.

If Column A (languages) has cells with the value "English", then move the entire row to Sheet2 which is named English
If Column A (languages) has cells with the value "German", then move the entire row to Sheet3 which is named German

Each row has names, phone number, email, etc.

This way i want to create a sheet with the contacts of each speaking language.

I've tried some home made VBA but i failed miserably. I could use any help.. Thank you very much! :D
 
Hi and welcome
might I suggest you search the Net first? This kind of problem has been asked and solved very often.
Using the title of your post as search item returned lots of hits
 
Pecoflyer said:
Hi and welcome
might I suggest you search the Net first? This kind of problem has been asked and solved very often.
Using the title of your post as search item returned lots of hits
Click to expand...
Hello i did search the web and tried lots of codes that return with errors because i'm not capable of identifying what i have to change rather than the sheet names
 
Please post a sample sheet, showing some ( eventually mock-up) data and expected results.

Also add in your post the code that you have found which looks like the best suited.To post a sheet, click "Go advanced - Manage attachments"Is your data range and the number of languages likely to increase in the future?Are the language sheets already created or should a new one be created each time a new language appears?Also be aware that ( depending on your layout and your needs) you can just filter your range for languages
 
Code: 
Public Function Allocate()
Dim this As Worksheet
Dim ws As Worksheet
Dim lastrow As Long
Dim nextrow As Long
Dim i As Long
    Application.ScreenUpdating = False
    
    Set this = ActiveSheet
    With this
    
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 2 To lastrow 'To 2 Step -1
        
            If Not SheetExists(.Cells(i, "A").Value) Then
            
                With .Parent
                
                    Set ws = .Worksheets.Add(after:=.Worksheets(.Worksheets.Count))
                    ws.Name = this.Cells(i, "A").Value
                    this.Rows(1).Copy ws.Range("A1")
                    nextrow = 2
                End With
            Else
            
                Set ws = .Parent.Worksheets(.Cells(i, "A").Value)
                nextrow = ws.Range("A1").End(xlDown).Row
                If nextrow = ws.Rows.Count Then nextrow = 1
                nextrow = nextrow + 1
            End If
            
            .Rows(i).Copy ws.Cells(nextrow, "A")
        Next i
        
        .Rows(2).Resize(lastrow - 1).Delete
    End With
    
    this.Activate
    Application.ScreenUpdating = True
End Function
Public Function SheetExists(ByVal Sheetname As String) As Boolean
Dim ws As Worksheet
    On Error Resume Next
    Set ws = Worksheets(Sheetname)
    SheetExists = Not ws Is Nothing
End Function
 
You must log in or register to reply here.
Back
Top