How do i extract merged data and put them into different worksheets?

Question

How do i use excel vba to extract merged data selectively(not manually clicking and copying from the excel sheet and also not specifically listing the range of the data that i want to copy. What i want is in a way such that the programme extract the entire row that has the same type of name from column F to column H for example Martin_1,Martin_2 and Martin _3 respectively for different names(John, Charlie) and copy and paste them to a new worksheet.

Put the extracted data into 3 different sheets namely index 1, index 2 and index 3 as shown below

Here is my attempted code:

dim i as integer
dim lastmartinrow as integer
dim c as string
dim j as integer
dim lastjohnrow as integer
dim b as string
dim k as integer
dim lastcharlierow as integer
dim a as string

for i = 1 to lastmartinrow
Set c = .Find("Martin_1","Martin_2",Martin_3" LookIn:=xlValues)
If c Is Nothing Then
'find the last row has the same "Martin_1","Martin_2","Martin_3") 
'copy the entire rows from 1 to lastmartinrow and paste it to a new worksheet("Index1").name
for j = lastmartinrow + 1 to lastjohnrow
Set b = .Find("John_1","John_2","John_3" LookIn:=xlValues)
If b Is Nothing Then
'find the last row has the same "John_1","John_2","John_3") 
'copy the entire rows from lastmartinrow + 1 to lastjohnrow and paste it to a new worksheet("Index2").name
for k = lastjohnrow + 1 to lastcharlierow
Set a = .Find("Charlie_1","Charlie_2",Charlie_3" LookIn:=xlValues)
If a Is Nothing Then
'find the last row has the same Charlie_1","Charlie_2",Charlie_3") 
'copy the entire rows from lastjohnrow + 1 to lastcharlierow and paste it to a new worksheet("Index3").name

error for the updated code Because previously i didnt save the spreadsheet when it crashed so this is the new sheet1 i am using right now.. [![enter image description here][6]][6]

Naveen Kumar · Accepted Answer · 2020-02-05T09:30:48.840

1

I have created a generic code, It will copy all the matching values(John,Marin,Charlie etc) present in F to H columns and paste it in Index3 sheet. It will not copy values with single row means which are not matching with any other row(immediately after that).

Sub UpdateVal()
    Static count As Long
    Dim iRow As Long
    Dim aRow As Long
    Dim a As Long
    Dim b As Long
    Dim selectRange As Range
    j = 2
    iRow = 1
    LastLine = ActiveSheet.UsedRange.Rows.count
    While iRow < LastLine + 1
        a = iRow + 1
        b = iRow + 17 ' Max Group Size with Same name in F to H column
        count = 1
        If Cells(iRow, "F").Value = "Martin1" Then
            sheetname = "Index1"
        ElseIf Cells(iRow, "F").Value = "John1" Then
            sheetname = "Index2"
        Else
            sheetname = "Index3"
        End If
        For aRow = a To b
            If Cells(iRow, "F") = Cells(aRow, "F") And Cells(iRow, "G") = Cells(aRow, "G") And Cells(iRow, "H") = Cells(aRow, "H") Then
                count = count + 1
            Else
                Set selectRange = Range("A" & iRow & ":J" & aRow - 1)
                selectRange.Copy
                indexrowcount = Sheets(sheetname).UsedRange.Rows.count
                Sheets(sheetname).Range("A" & indexrowcount).PasteSpecial xlPasteAll
                iRow = iRow + count
                Exit For
           End If
        Next aRow
    Wend
End Sub

edited Feb 05 '20 at 09:30

answered Feb 05 '20 at 05:33

Naveen Kumar

1,988
1
7
11

that is an error with the code such that when i run it, it will cause the excel to crash – cena Feb 05 '20 at 06:06
Obviously, I didn't give the code to crash your excel. what error are you getting? – Naveen Kumar Feb 05 '20 at 06:08
17 rows for sheet1 – cena Feb 05 '20 at 06:13
instead of screen shot, can u please add plain text? – Naveen Kumar Feb 05 '20 at 06:14
u mean description of the screenshot also? – cena Feb 05 '20 at 06:17
No i am talking about the excel data which contains 17 rows. – Naveen Kumar Feb 05 '20 at 06:18
i just posted the picture of my sheet1 – cena Feb 05 '20 at 06:20
I will not be able to copy text from picture. I want to test code on your data. – Naveen Kumar Feb 05 '20 at 06:22
Try the updated code. I have tested it with your excel data. It is working fine. – Naveen Kumar Feb 05 '20 at 06:33
do we have to dim anything like sheetname,indexrowcount or something else? And do we have to change the sheetname into different sheetnames like sheetname1 ,sheetname2 sheetname3 for different indexes?Because i still faced the same error that i mentioned as shown on the above post... – cena Feb 05 '20 at 06:55
Without Dim it will work. And obviously we are using Index1 , Index2 and Index3 sheets in code so all these sheets should be there in your excel. – Naveen Kumar Feb 05 '20 at 06:57
i have already created the index1,index 2 and index 3 worksheet before running the macro but it just shows the same error ;( – cena Feb 05 '20 at 07:00
It should be Index1(with capital I) and similarly for Index2 & Index3. You can see the sheet names in code. Mention as it is. – Naveen Kumar Feb 05 '20 at 07:02
yup i did put the captical I in my indexes worksheet name and same error still ocurred as shown on my above post – cena Feb 05 '20 at 07:05
Delete the existing content from Index1, Index2 and Index3 and then execute the code. I thinks existing content is there in these sheets, so it is appending. – Naveen Kumar Feb 05 '20 at 07:09
to make sure there is no content, i went to delete and create the index 1 to index3 worksheet but same problem still persists – cena Feb 05 '20 at 07:14
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/207244/discussion-between-naveen-arora-and-cena). – Naveen Kumar Feb 05 '20 at 07:37

How do i extract merged data and put them into different worksheets?

1 Answers1

Linked