Expand existing Copy-Paste Loop: Loop through specific Columns based on Cell Value - Questionnaire

[BuRnZz]

I currently have multiple excel spreadhsheets that look like this:

The table is a questionnaire with answers from column C-F, C is the "worst" (letter N as in Not good), D the "second worst" (letter T), E the second best (letter W) and F the best (letter G as in Good).

To the right of this table are sentences that I copy to another Spreadsheet using an existing module, depending on where the "x" in the questionnaire is set (it always copies the sentence 9 rows to the right of the "x").

Now I want to modify my existing code to not just copy all the sentences from every row, but only copy 5 for each Worksheet. These 5 should be either the 5 "best" answers (5 from column F, meaning Good as answer in questionnaire, if there are less than 5 in that column then take the rest from column E until you have 5) or the 6 "worst" answers, meaning 5 from column C(letter N as in not good) and if there are less than 5 "X" in that column take the rest from column D (letter T). This way I want to copy the 5 best or worst answers for each Worksheet. The decision if the best or worst answers should be copied depends on one simple Cell Value (Cell K6) in each Worksheet. If K6 >70% take the best answers, if K6 is under 70%, take the worst answers.

This is my current module to copy all the answers to my new worksheet:

Dim ws As Worksheet
Dim lr As Integer 'lastrow
Dim SpaltenIndex As Integer
Dim SheetNummer As Integer
Dim cl As Range 'cell
Dim rw As Range 'row
Dim Antwortrange As String
Dim WrkSht As Worksheet
Dim WrkShtCol As Sheets


'Create Destination Sheet
Sheets.Add
ActiveSheet.Name = "Handlungsempfehlungen"

'Set Questionnaire Answer Range to search through
Antwortrange = "C11:F400"

'ColumnIndex and SheetNumber
SpaltenIndex = 1
SheetNummer = 1

'Create Worksheet Collection with all the Questionnaire-Sheets
Set WrkShtCol = Worksheets(Array("AM AD - Anforderungsdefinition", "AM AA - Anforderunganalyse", "AM - Anforderungsdokumentation", "AM AV - Anforderungsvalidierung", "TM IT - Initiierung Test", "TM ZD - Zieldefinition", "TM TV - Testvorgehen", "TM TOB - Testobjektabgrenzung", "TM AS - Aufwandsschätzung", "TM TP - Testplanung", "TM TP - Testplanung", "TM TA - Testauftrag", "TM TS - Teststeuerung", "TM AO - Aufbauorganisation", "TM RM - Risikomanagement", "TM MI - Managementinformation", "TM AF - Abnahme Freigabe", "TM AT - Abschluss Test", "DT IT - Installationstest", "DT ST - Sicherheitstest", "OTP DT - Dokumententest", "OTP MT - Modultest", "OTP MIT - Modulintegrationstest", "OTP OO KT - OO Klassentest", "OTP OO KIT - OO Klassenintgrate", "OTP FT - Funktionstest", "OTP FIT - Funktionsintgratiotes", "OTP PIT - Produktintegratest", "OTP AT - Abnahmetest", "OTP ET - Ergonomietest", "OTP LPT - Last & Performance", "OTP GPT - Geschäftsprozesstest", "TUP TMK -Testumg Module Klassen", _
"TUP TUF - Testumgebung Funktion", "TUP TP - Testumgebung Prozesse", "ATP KM Konfigurationsmanagement", "ATP FAEM - Fehler Änderungs", "ATP DS - Datensicherheit", "ATP DSCH - Datenschutz", "ATP TEV -Testergebnisverwaltung", "ATP VG - Vertragsgestaltung"))
    
'MAIN LOOP: Take all sentences 9 rows to the right of each X in each Questionnaire and paste the value to the newly created sheet from above

For Each WrkSht In WrkShtCol

    For Each rw In WrkSht.Range(Antwortrange).Rows   
    For Each cl In rw.Cells
        
    lr = ws.Cells(ws.Rows.Count, SpaltenIndex).End(xlUp).Offset(1).Row
    If lr = 2 And ws.Range("A1") = "" And lr < 500 Then lr = 1
    'If lr = 2 And ws.Range("A2") = "" Then lr = 1
       
            If LCase(cl.Value) = "x" Then
                cl.Offset(0, 9).Copy Sheets("Handlungsempfehlungen").Cells(lr, SpaltenIndex)       
            End If
        Next cl
    Next rw



'If 1st row is empty in destination sheet, delete and shift rest up 
If Sheets("Handlungsempfehlungen").Cells(1, SpaltenIndex) = "" Then Sheets("Handlungsempfehlungen").Cells(1, SpaltenIndex).Delete Shift:=xlUp

'WrkShtCol(1).range("A2").Copy Worksheets("Handlungsempfehlungen").Cell(lr, SpaltenIndex)

Sheets("Handlungsempfehlungen").Cells(35, SpaltenIndex).Value = WrkShtCol(SheetNummer).Cells(2, 1)


SpaltenIndex = SpaltenIndex + 1
SheetNummer = SheetNummer + 1


End Sub

I hope you can help me, any tips would be greatly appreciated. Thank you so much in advance.

Edit - Expected Results:

If K6 is over 70% - find the 5 best answers (1st priority column F, if there are 5 "x" in column F, find those Cells and copy the value 9 rows to the right to the new sheet.

So if the questionnaire looks like this:

The pasted table should look like this:

And if the questionnaire is under 70%, do the same but for the worst (Column C & D, C being the worst, if there are not 5 "x" in C then take the rest from D (second worst))

Hope this helps
Thank you so much

 

[BuRnZz]

Will try!

 

[BuRnZz]

Delete the two sample sheets that show the results. In cell K6 of most of your sheets you have a formula that is returning an error. Either fix the formula to return a valid percentage or manually enter the percentage otherwise the macro will return an error because there is no value to compare. Try this macro.

Thank you so much. I sadly get an "Type Mismatch"-Error on the line

If .Range("K6") >= 0.7 Then

But for the first Sheet it did it perfectly already!!!

Edit: This is the result: Gyazo
First table worked fine, second table it chose all of the X-values in Clumn F & E, copying 17 values instead of 5

 

[BuRnZz]

Delete the two sample sheets that show the results. In cell K6 of most of your sheets you have a formula that is returning an error. Either fix the formula to return a valid percentage or manually enter the percentage otherwise the macro will return an error because there is no value to compare. Try this macro.

Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, desWS As Worksheet, ws As Worksheet, lCol As Long
    If Not Evaluate("isref('" & "Handlungsempfehlungen" & "'!A1)") Then
        Sheets.Add(before:=Sheet1).Name = "Handlungsempfehlungen"
        Set desWS = Sheets("Handlungsempfehlungen")
    Else
        Set desWS = Sheets("Handlungsempfehlungen")
        desWS.UsedRange.ClearContents
    End If
    For Each ws In Sheets
        If ws.Name <> "Handlungsempfehlungen" And ws.Name <> "Steps" And ws.Name <> "Changelog" Then
            With ws
                lCol = desWS.Cells(1, Columns.Count).End(xlToLeft).Column + 1
                LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
                If .Range("K6") >= 0.7 Then
                    If WorksheetFunction.CountIf(.Range("F11:F" & LastRow), "x") >= 5 Then
                        .Range("A2").AutoFilter 6, "x"
                        desWS.Cells(1, lCol) = ws.Name
                        .Range("F11:F" & LastRow).Offset(, 9).SpecialCells(xlCellTypeVisible).Copy desWS.Cells(desWS.Rows.Count, lCol).End(xlUp).Offset(1)
                        .Range("A2").AutoFilter
                    Else
                        .Range("A2").AutoFilter 6, "x"
                        desWS.Cells(1, lCol) = ws.Name
                        .Range("F11:F" & LastRow).Offset(, 9).SpecialCells(xlCellTypeVisible).Copy desWS.Cells(desWS.Rows.Count, lCol).End(xlUp).Offset(1)
                        .Range("A2").AutoFilter
                        .Range("A2").AutoFilter 5, "x"
                        .Range("E11:E" & LastRow).Offset(, 9).SpecialCells(xlCellTypeVisible).Copy desWS.Cells(desWS.Rows.Count, lCol).End(xlUp).Offset(1)
                        .Range("A2").AutoFilter
                    End If
                Else
                    If WorksheetFunction.CountIf(.Range("C11:C" & LastRow), "x") >= 5 Then
                        .Range("A2").AutoFilter 3, "x"
                        desWS.Cells(1, lCol) = ws.Name
                        .Range("C11:C" & LastRow).Offset(, 9).SpecialCells(xlCellTypeVisible).Copy desWS.Range("A2")
                        .Range("A2").AutoFilter
                    Else
                        .Range("A2").AutoFilter 4, "x"
                        desWS.Cells(1, lCol) = ws.Name
                        .Range("C11:C" & LastRow).Offset(, 9).SpecialCells(xlCellTypeVisible).Copy desWS.Cells(desWS.Rows.Count, lCol).End(xlUp).Offset(1)
                        .Range("A2").AutoFilter
                        .Range("A2").AutoFilter 5, "x"
                        .Range("D11:D" & LastRow).Offset(, 9).SpecialCells(xlCellTypeVisible).Copy desWS.Cells(desWS.Rows.Count, lCol).End(xlUp).Offset(1)
                        .Range("A2").AutoFilter
                    End If
                End If
            End With
        End If
    Next ws
    With desWS
        LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        .Rows("7:" & LastRow).Delete
        .Columns(1).Delete
        .Columns.AutoFit
    End With
    Application.ScreenUpdating = True
End Sub

Ok, so this works and the typing mismatch was my bad, didnt fix all of the formulas in K6 on the other Spreadsheets. The two big things that dont work yet are:
-only choose 5 values (either best or worst, did this for the first worksheet but not the rest)
-it always copys the 5 BEST values, even if K6 is under 0.7, where it is supposed to take the worst (up to 5 from column C, Rest from D)

 

[BuRnZz]

It does not always take the best values. It works with K6 < 0.7 as well if I format K6 correctly before (my bad again..)
Only thing is it copies more than 5 values for each sheet (except sheet 1).

 

[BuRnZz]

Ok i tried the code with my orignal full project and everyhting works. Sorry for all the dumb replies earlier, you are my god and savior. thank you so much

 

[mumps]

My pleasure.