Copy pasting columns based on headers

Hi, krash297,

insetad of switching sheets set object to the sheets. And instead of having multiple codes for each item use an array which holds the values and loop through that array:

Sub EF938993()
Dim arrFinds
Dim lngArray As Long
Dim wsSign As Worksheet
Dim wsSgnot As Worksheet
Dim rngFound As Range
Dim lngCol As Long
Dim lngLast As Long

arrFinds = Array("Project", "Activity", "Activity and Work Area", "WA Stage", "Status", _
        "QIL Error Rate", "QQ Error Rate", "Start Date WA", "End Date WA")
    
Set wsSign = Sheets("Signout")
Set wsSgnot = Sheets("Sgnotproces")

For lngArray = LBound(arrFinds) To UBound(arrFinds)
  Set rngFound = wsSign.Cells.Find(arrFinds(lngArray), , , , xlByRows, xlNext, True, , False)
  If Not rngFound Is Nothing Then
    lngLast = wsSign.Cells(Rows.Count, rngFound.Column).End(xlUp).Row
    lngCol = lngCol + 1
    wsSgnot.Range(wsSgnot.Cells(1, lngCol), wsSgnot.Cells(lngLast, lngCol)).Value = _
        wsSign.Range(wsSign.Cells(1, rngFound.Column), wsSign.Cells(lngLast, rngFound.Column)).Value
  End If
Next lngArray
wsSign.UsedRange.ClearContents

Set wsSgnot = Nothing
Set wsSign = Nothing
End Sub

Ciao,
Holger

Hi, krash297,

the array stores the names of the items you want to search. In the code it is filled with the line

arrFinds = Array("Project", "Activity", "Activity and Work Area", "WA Stage", "Status", _
        "QIL Error Rate", "QQ Error Rate", "Start Date WA", "End Date WA")

Any item should be packed in quotes (it it is a string) and be separated by comma. This type of array is zero based which means that the first item will have the number of 0.

Next we do have a loop which will go from the lower bound of the array (as I just stated this will be 0) to the number of items being stored - 1. Instead of writing numbers we use the command LBound (lower bound) and UBound (upper bound) which will free us to alter the code if the number of the items to search for is changed.

Ciao,
Holger