Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-15-2022, 09:50 AM
Venteux Venteux is offline Loop through headers in column, find all instances of specific header name Windows 10 Loop through headers in column, find all instances of specific header name Office 2019
Novice
Loop through headers in column, find all instances of specific header name
 
Join Date: May 2021
Posts: 22
Venteux is on a distinguished road
Default Loop through headers in column, find all instances of specific header name

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
Reply With Quote
  #2  
Old 06-16-2022, 09:47 AM
p45cal's Avatar
p45cal p45cal is offline Loop through headers in column, find all instances of specific header name Windows 10 Loop through headers in column, find all instances of specific header name Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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
Reply With Quote
  #3  
Old 06-16-2022, 10:00 AM
Venteux Venteux is offline Loop through headers in column, find all instances of specific header name Windows 10 Loop through headers in column, find all instances of specific header name Office 2019
Novice
Loop through headers in column, find all instances of specific header name
 
Join Date: May 2021
Posts: 22
Venteux is on a distinguished road
Default

YES!! Thank you!!!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Loop through headers in column, find all instances of specific header name Enclose All Instances of a Specific Custom Style in Brackets Matt C Word VBA 7 05-30-2021 04:52 AM
Loop through headers in column, find all instances of specific header name 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

Other Forums: Access Forums

All times are GMT -7. The time now is 12:48 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft