Loop sheets, columns, rows and copy value from sheet to correct cell

[Dalvin]

Hi,

I have written a script that, is intended to, for each row in a range find a specific sheet, then in that sheet find the correct column, then the row that matches two criteria from the source sheet row. The inner code is supposed to copy a value from the row in the source sheet and paste it in the correct sheet-column-row.

Dim srcrow As Long 'Variable for row loop in source sheet
Dim sht As Long 'Variable for sheet loop
Dim col As Long 'Variable for column loop in target sheet
Dim row As Long 'Variable for row loop in target sheet
Dim LastCol As Long
Dim LastRow As Long
LastRow = Sheets("qPCR temp").Cells(Sheets("qPCR temp").Rows.Count, 1).End(xlUp).Column
For srcrow = 2 To LastRow
For sht = 7 To Sheets.Count
    If sht = Sample Then
    LastCol = Sheets(sht).Cells(15, Sheets(sht).Columns.Count).End(xlToLeft).Column
        For col = 1 To LastCol
            If Sheets(sht).Cells(15, col) = test Then
                For row = 19 To 386
                    If Sheets(sht).Cells(row, 9).Value = Sheets("qPCR temp").Cells(srcrow, 1) And Sheets(sht).Cells(row, 10).Value = Sheets("qPCR temp").Cells(srcrow, 2) Then
                    Sheets(sht).Cells(srcrow, 3).Copy Sheets(sht).Cells(row, col)
                    End If
                Next row
            End If
        Next col
    End If
Next sht
Next srcrow

The "sample" and "test" string has been declared and assigned values earlier in the sub.

My problem is that the code runs without any error messages but no values are copied anywhere.
I'm new here so I don't know the convention about asking for help with code, but does anyone see any obvious mistakes as to why the code isn't returning what I want it to?

Thanks for the help!

 

[Fluff]

Hi & welcome to MrExcel
How about changing

If sht = Sample Then

to

If sheets(sht).Name = Sample Then

 

[Dalvin]

Thanks for the reply!

I tried it that way now, but unfortunately the result is the same

 

[Fluff]

Are you trying to copy from the "qPCR temp" sheet?

 

[Dalvin]

Yes that is correct.

 

[Fluff]

In taht case this

Sheets(sht).Cells(srcrow, 3).Copy Sheets(sht).Cells(row, col)

should be

Sheets("qPCR temp").Cells(srcrow, 3).Copy Sheets(sht).Cells(row, col)

 

[Dalvin]

haha yes this is very true, I didn't notice that.

Unfortunately still no copied values

 

[Dalvin]

More specifically, the "qPCR temp" sheet contains 352 rows that each have a value that should be copied to a row in the same variable sheet and the same variable column. So neither the sheet nor column will differ in any of the 352 copied values, only the row.

 

[Fluff]

With all those nested loops, it's impossible to figure out what's happening.
A few questions.
1) Does "Sample" change?
2) Does "Test" change?
3) Will "Test" only appear once in row 15?