I'm trying to write a code that will search through specified sheets and copy certain columns and paste them in a new worksheet. This code I have so far:
what i need right now is for it to say if the ss(sourcesheet) is Rolls 43 or Rolls 43D then change h1 to rolls and then look through the first row and if the cell is Rolls,Brand, Roll Diameter, Code, or Width then copy these columns and then paste in the newley made sheet in the next open column.
Sub pivot()
Dim ss As Worksheet
Dim i As Long
On Error Resume Next
Application.ScreenUpdating = False
For Each ss In ThisWorkbook.Worksheets(Array("Rolls 43", "Rolls 43D"))
', "Rolls 45", "Rolls 45D", "Rolls 47", "Rolls 47D", "Rolls 48", "Rolls 48D"))
    With ss
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Combined 43"
         For i = .Range("1" & .Columns.Count).End(xlLeft).Column To A Step -1
            If ss = "Rolls 43" Or "Rolls 43D" Then
             Range("H1") = Rolls
                
                If (Left(.Range("1" & i).Value, 1) = "Rolls" Or Left(.Range("1" & i).Value, 1) = "Rolls" Or Left(.Range("1" & i).Value, 1) = "Brand" Or Left(.Range("1" & i).Value, 1) = "Code" Or Left(.Range("1" & i).Value, 1) = "Roll Diameter") Then
                 .Columns(i).xlDown.Copy
                    Sheets("Combined 43").Select
                    Rows("1").Find("", Cells(Columns.Count, "1")).Paste
                End If
            End If
        Next i
    End With
Next
Application.ScreenUpdating = True

End Sub