0

I have a sheet with some data and another sheet that is empty with just column headers. I wanted to copy data from the initial sheet into the other sheet based on a criteria where the region should be "Africa".

I used the code below which is copying the data but it is copying the first row from the initial sheet multiple times and not all the data rows where the region is "Africa". Here is a snippet of my code:

'Assigning the arrays to variables to return column index number
    ws1Headers = getIndexes(ws1.Rows(4), mHeaders)
    ws2Headers = getIndexes(ws2.Rows(2), soHeaders)
    
    'Setting first and last row for the columns in both sheets
    ws1SORow = 5              'The row we want to start processing first
    ws1EndRow = ws1.UsedRange.Rows(ws1.UsedRange.Rows.count).Row
    ws2SORow = 3              'The row we want to start search first
    ws2EndRow = ws2.UsedRange.Rows(ws2.UsedRange.Rows.count).Row

   'iterate through search terms
    For i = ws1SORow To ws1EndRow     'first and last row
        searchKey = ws1.Range("A" & i)

        If (searchKey = "") Then
            For j = ws2SORow To ws2EndRow    'first and last row
                foundKey = ws2.Range("O" & j)
                  'Copy result if there is a match
                 If (foundKey = "Africa") Then
                    'Copying data where the headers match
                    For k = LBound(ws2Headers) To UBound(ws2Headers)
                        ws1.Cells(i, ws1Headers(k)) = ws2.Cells(j, ws2Headers(k))
                    Next k
                    Exit For
                 End If
            Next
        End If
    Next

I have a function that gets the index of the headers and the header names are also defined which I have not included as it is a lot of code. Would highly appreciated any help for the query posted.

rustyBucketBay
  • 4,320
  • 3
  • 17
  • 47
Shuri1812
  • 5
  • 4
  • Isn't [THIS](https://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s/11633207#11633207) what you are trying? – Siddharth Rout Nov 19 '20 at 08:01
  • I am confused by that code as I am just a beginner. Also, I have to copy multiple column data and the columns are not consecutive. – Shuri1812 Nov 19 '20 at 08:08

0 Answers0