Excel macro copy from a single sheet to different sheet

[Jyotirmaya]

I am using the below code to copy data from a sheet to different sheets if the sheet name matches with text of column E

Sub CopyToSheets()

Dim ws As Worksheet
Dim ws2 As Worksheet
Dim lastrow As Long
Dim lastrow2 As Long
Dim rownum As Long
Dim ws2name As String

Set ws = Sheets("RAW DATA")
lastrow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row

For rownum = 2 To lastrow
    ws2name = ws.Cells(rownum, 5)
    Set ws2 = Sheets(ws2name)
    lastrow2 = ws2.Cells(ws2.Rows.Count, "E").End(xlUp).Row
    ws.Rows(rownum).copy ws2.Rows(lastrow2 + 1)
Next rownum

End Sub

I have data in RAW DATA sheet and I am copying data from this sheet to other by using this macro. I have data till column H in RAW DATA sheet, now in a new column added for example if in column H Text is there as "ABC" in any row then I want that row shouldn't be copied to other sheets & it should be copied to sheet name "ABC" instead of the sheet name mentioned in column E, for this what should be the change in the code ??

 

[Fluff]

How about

For rownum = 2 To lastrow
    If ws.Cells(rownum, 8) = "ABC" Then
        ws2name = "ABC"
    Else
        ws2name = ws.Cells(rownum, 5)
    End If
    Set ws2 = Sheets(ws2name)
    lastrow2 = ws2.Cells(ws2.Rows.Count, "E").End(xlUp).Row
    ws.Rows(rownum).Copy ws2.Rows(lastrow2 + 1)
Next rownum

 

[Jyotirmaya]

Thanks @Fluff

Kindly let me know what to add in code if I need to add a new sheet for text XYZ in row number 8 along with ABC

 

[Fluff]

How about

For rownum = 2 To lastrow
    Select Case ws.Cells(rownum, 8)
      Case "ABC"
         ws2name = "ABC"
      Case "XYZ"
         ws2name = "XYZ"
      Case Else
        ws2name = ws.Cells(rownum, 5)
    End Select
    Set ws2 = Sheets(ws2name)
    lastrow2 = ws2.Cells(ws2.Rows.Count, "E").End(xlUp).Row
    ws.Rows(rownum).Copy ws2.Rows(lastrow2 + 1)
Next rownum

 

[Jyotirmaya]

Excellent, thank you @Fluff

 

[Fluff]

You're welcome & thanks for the feedback.