Jyotirmaya
Board Regular
- Joined
- Dec 2, 2015
- Messages
- 205
- Office Version
- 2019
- Platform
- Windows
VBA Code:
Private Sub CommandButton1_Click()
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
Select Case ws.Cells(rownum, 9)
Case "Fruit"
ws2name = "Fruit"
Case "Vegetable"
ws2name = "Vegetable"
Case "Nut"
ws2name = "Nut"
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
End Sub
I am using the above code to transfer data from RAW DATA sheets to different sheets based on the values of Column E and again if there is text "Fruit" or "Nut" or "Vegetable" then the data will be copied into those sheets, now I want that FOR EXAMPLE if there is text "Fruit" in Column I and in Column E against that row if text "APPLE" and if there is a sheet name " Fruit APPLE" then the data will be copied into Fruit sheet as well as Fruit APPLE sheet. Here I have used APPLE as an example in my data there are 100+ types of values in column E, so I want that if column H sheet name + column E sheet names matches then those data will be copied to those sheet name also. Kindly hep me with the code.