Loop through headers in column, find all instances of specific header name

Venteux · #1 06-15-2022, 09:50 AM

Hello,

I'd like to loop through all headers in each column, and copy and paste all data below the header into another column. The header name may appear more than once in the columns and I can't figure out how to loop through all of the columns and find all instances of the header name.

This is what I have so far, but it only finds the first instance of the header name (e.g. coursename) and then stops.

Any help is appreciated!

Code:

Sub Courses()



Dim LRow As Long
Dim Found As Range


With Sheets("Sheet1")



Set Found = .Range("A1:AB1").Find("coursename")


If Not Found Is Nothing Then
    LRow = .Cells(.Rows.Count, Found.Column).End(xlUp).Row
    .Range(.Cells(2, Found.Column), .Cells(LRow, Found.Column)).Copy
    .Cells(.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If


End With


End Sub

p45cal · #2 06-16-2022, 09:47 AM

Something like this?:

Code:

Sub Courses()
Dim LRow As Long
Dim Found As Range

With Sheets("Sheet1")
  Set Found = .Range("A1:AB1").Find("coursename", LookIn:=xlValues, lookat:=xlWhole, searchformat:=False)
  If Not Found Is Nothing Then
    Set FirstFound = Found
    Do
      LRow = .Cells(.Rows.Count, Found.Column).End(xlUp).Row
      .Range(.Cells(2, Found.Column), .Cells(LRow, Found.Column)).Copy
      .Cells(.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
      Set Found = .Range("A1:AB1").Find(what:="coursename", after:=Found, LookIn:=xlValues, lookat:=xlWhole, searchformat:=False)
    Loop Until Found Is Nothing Or Found.Address = FirstFound.Address
  End If
End With
End Sub

Venteux · #3 06-16-2022, 10:00 AM

YES!! Thank you!!!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Enclose All Instances of a Specific Custom Style in Brackets	Matt C	Word VBA	7	05-30-2021 04:52 AM
Find a specific number pattern in a column	DJ0691	Excel	5	02-13-2020 10:47 AM
How To Find Highest Value In A Row And Return Column Header	ballpoint	Excel	1	02-23-2018 11:18 AM
Loop through a column	k7dm	Excel	9	09-25-2016 11:32 AM
Find all instances of .0%	foxtrot	Word	8	11-18-2014 08:17 AM