Copy values from one sheet to another using Data from UserForm

Question

I have a Userform which contains the following values that you can fill in:

TextBoxLopnummer.Value
TextBoxFragestallare.Value
TextBoxMottagare.Value
TextBoxDatum.Value

Picture:

When somebody fills in the date value : TextBoxDatum.Value I want to search this value in the whole workbook and paste the whole row in which that cell is, inside of Sheet "Lägg in Ärende" cell A15. Note that this value can be in diffrent sheets in the workbook and appear several times inside of the same sheet. So in Cell A15 and below there can be alot of rows.

I have started implementing this a bit but I honestly dont have any idea on how to finish it:

'in the rows below I wanna write so that ".Value=copies the value from the sheets where it finds eg. the date".
emptyRow = WorksheetFunction.CountA(ws.Range("A:A")) + 14
Cells(emptyRow, 1).Value =
Cells(emptyRow, 2).Value =
Cells(emptyRow, 3).Value =
Cells(emptyRow, 4).Value =
Cells(emptyRow, 5).Value =
Cells(emptyRow, 6).Value =
Cells(emptyRow, 7).Value =
Cells(emptyRow, 8).Value =

Note that you can search for more then the date at the same time, there are 4 criterias that you can search on, see the picture above. When you fill in 2 criterias then the code should match these two with the row in the entire workbook that has the same criterias and copy that row, etc.

Also TextBoxLopnummer will always be in cell A2 and downwards in the sheets where its being searched in. TextBoxFragestallare in cell B2, TextBoxMottagare in cell C2, TextBoxDatum in cell D2.

How can I continue with solving my issue?

Answer 1

This is going to get you most of the way to what you are trying to do. Based on the comments in your original question I believe this is what you need.

Process:

• Have a click event for the search button on the UserForm code. In the example, it's Button1. Name it according to your own needs.

• Clear the target sheet before each run (per requests)

• Set an array from the textbox values where the index of each value matches the column number to search

• Loop through each worksheet, except the target sheet.

• One row at a time, compare the value of the appropriate column to the array index that matches it.

• If a match is found, the "match" variable is set to true

• Loop through the rest of the TextBoxes values from the array, if ANY of them don't match, the "match" variable is set to false, and break the loop over the Textboxes as a fail.

• If "match" is true by the end of the loop through the ROW of the Searched worksheet, columns 1 to 8 get looped through, setting the values from the searched sheet to the target Sheet.

• Next Row Finish loop

• Next Worksheet finish loop

Possible issues to check:

• You might have to do some conversions of dates, but if the dates on the sheets are in the same format as the dates on the user form, it should work.

• Numbers might provide similar issues if the text in the sheet has a 0.0 or varying decimal places.

• If any issues like this occur, just use your Locals Window and step through your code to see it execute. The likely error you'd receive for something like that would be a type mismatch. By debugging with the Locals window, you will know which specific values need formatted in order to compare them with the text box. Put a break point if stepping through is too long.

Untested: comment with problems.

Private Sub button1_click()

Dim ws As Worksheet
Dim lastRow As Long, lRow As Long, tRow As Long
Dim tempValue As String
Dim targetSheet As String
Dim tempList(1 To 4) As String
Dim i As Long
Dim match As Boolean

match = False

'Set TargetSheet and clear the previous contents
targetSheet = "Lägg in Ärende"
tRow = 15
lastRow = Sheets(targetSheet).Range("A" & Rows.count).End(xlUp).row
Sheets(targetSheet).Range("A15:H" & lastRow).ClearContents

'Set an array of strings, based on the index matching the column to search for each
tempList(1) = TextBoxLopnummer.Text       'Column "A" (1)
tempList(2) = TextBoxFragestallare.Text   'Column "B" (2)
tempList(3) = TextBoxMottagare.Text       'Column "C" (3)
tempList(4) = TextBoxDatum.Text           'Column "D" (4)

    'Search through each worksheet
    For Each ws In Worksheets
        If ws.name <> targetSheet Then
            'Get last row of sheet
            lastRow = ws.Range("A" & Rows.count).End(xlUp).row

            'Search through the sheet
            For lRow = 2 To lastRow
                'Using the array of values from the TextBoxes,
                'Each column number matches the index of the array.
                'Only testing the array values that have text in them,
                'If any don't match the loop is broken and returns to main search.
                For i = 1 To 4
                    If tempList(i) <> "" Then
                        If ws.Cells(lRow, i).Text = tempList(i) Then
                            match = True
                        Else
                            match = False
                            Exit For        'If any of the values is false, exit i loop
                        End If
                    End If
                Next i

                'If there was a match, copy the data from Searched ws to targetSheet
                If match = True Then
                    'Get the first Empty row on target sheet
                    For lCol = 1 To 8
                        Sheets(targetSheet).Cells(tRow, lCol).Value = ws.Cells(lRow, lCol).Value
                    Next lCol
                    tRow = tRow + 1
                End If
            Next lRow
        End If
    Next ws

End Sub