Hi, I'm Carol and I'm brand new to the Forums.

My position requires me to take variable number of samples from existing reports and analyze them for compliance with our standards. I get about 13 different reports each month, and I've gotten a lot of great information from this and other forums (mostly this one) on how to do most of what I need to do, such as: grab a random sample based on how many I need, copy the sample over to a new sheet (creating it if it doesn't already exist), and grabbing the corresponding cells of the sample items for the data points I need to analyze. This forum has been an absolute lifesaver, and I've been able to find most of what I need just by searching.

My problem is automating the last part. Right now, I use the same module on all my reports that

1. asks me how many items I want to pull from the active sheet
2. asks me on what sheet do I want the results and
3. pastes a random number of those unique ID #s from the original sheet to the target sheet with no change in the code.

I also have it pull 3-4 corresponding cells from the original sheet that in the same row as each unique ID # and paste them on the target sheet, but I have to manually tell it which cells to compare and copy, and I have to manually tell it from which cells to get the headers for that data (for example, on one report the Unique ID # is an investigation #, then I have three criteria that I have to analyze "Accuracy" "Completeness" and "Documented") that sits among several other cells with info I don't need to look at and therefore don't need to copy from the original to the target sheet.

So what I want to do is ask "How many headers will you need?"

Take that variable (say it's 3) and then ask

What do you want to name Header 1? And put Header 1 in to Cells (1, 2) on target sheet, then say I name it "Accuracy" and "Accuracy" is part of the header on the original sheet. I then what it to know that it's taking the corresponding data from the the row of unique ID # (call it 12345678) and the same column as the "Accuracy" header on that original sheet and move it to the target sheet.

I then want it to ask "What do you want to name Header 2? and do the same thing with Cells (1, 3) on the target sheet.

Here is my code for the manual part of that task

    
    Worksheets(targsheet).Columns(1).Clear
    Worksheets(targsheet).Cells(1, 1) = Worksheets(origsheet).Cells(5, 5)
    Worksheets(targsheet).Cells(1, 2) = Worksheets(origsheet).Cells(4, 7)
    Worksheets(targsheet).Cells(1, 3) = Worksheets(origsheet).Cells(4, 13)
    Worksheets(targsheet).Cells(1, 4) = Worksheets(origsheet).Cells(4, 17)
    
    'Worksheets(targsheet).Cells(1, 5) = Worksheets(origsheet).Cells(1, 14)
    
    'now we populate the target sheet using the row numbers placed into the results array
    
    For i = 1 To NbrItems
        Worksheets(targsheet).Cells(i + 1, 1) = Worksheets(origsheet).Cells(Results(i), 5)
    Next i
    
Set sh1 = Worksheets(targsheet)
Set sh2 = Worksheets(origsheet)

lastrow1 = sh1.Cells(Rows.Count, "A").End(xlUp).Row
lastrow2 = sh2.Cells(Rows.Count, "A").End(xlUp).Row

For k = 2 To lastrow1
For h = 2 To lastrow2

If sh1.Cells(k, "A").Value = sh2.Cells(h, "E").Value Then

sh1.Cells(k, "B").Value = sh2.Cells(h, "G").Value
            
End If
Next h
Next k

For k = 2 To lastrow1
For h = 2 To lastrow2

If sh1.Cells(k, "A").Value = sh2.Cells(h, "E").Value Then

sh1.Cells(k, "C").Value = sh2.Cells(h, "M").Value
            
End If
Next h
Next k

For k = 2 To lastrow1
For h = 2 To lastrow2

If sh1.Cells(k, "A").Value = sh2.Cells(h, "E").Value Then

sh1.Cells(k, "D").Value = sh2.Cells(h, "Q").Value
            
End If
Next h
Next k
Any help is appreciated.

Sincerely,

Carol