Hi,

I have this macro that obtains values from different sheets and pastes in one sheet called "Lägg in och sök ärende". These values gets copied based on som indata that you insert in a user form, see picture below:

Excel1.PNG

The macro is correct programmed so far but I want it to skip one sheet called "Frågor - sammanställning". How can I write this in the code so that it ships this sheet specific.

And also how can I in Sheet("Lägg in och sök ärende").cell("H15") paste the name of that sheet that it obtains these values from?

Code:

Private Sub Lagginarende_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 och sök ärende"
tRow = 15
lastRow = Sheets(targetSheet).Range("A" & Rows.Count).End(xlUp).Row
If lastRow < 15 Then
   lastRow = 15
End If
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

Unload Me
End Sub
Private Sub Avbryt2_Click()
Unload Me
End Sub
Private Sub Rensa2_Click()
Application.ScreenUpdating = False
Unload Me
SokEfterArende.Show
Application.ScreenUpdating = True

End Sub