Moving Columns - want to have a macro that finds certain ones

[SendHalp3]

Hi all!

I want to create a macro that will find a certain column, based on the value of a cell in that column, and copy/paste values the data from that column to another sheet.

The reason why i need it to find based on a value of a cell is because i have "customizable"/"moving" columns and they may not be universal for all managers or could change, and i want to minimize the risk of error.

I keep the values in a specific row (row4).

Would anyone have any idea on how to create something like this? Thank you for any guidance!


Heres a piece of an example of what I have for another parts of this that aren't as complicated as what I'm looking for (this is in a module of the workbook using a button/shape to run). Basically I'm wondering how do i find the Range(?) because the Range might not be specifically column E (or any other letter) (I apologize I'm self teaching and I'm almost at the home stretch of this!)

Sub Example()
Application.ScreenUpdating = False
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws

Sheets("Sheet1").Select
Range("E7:E1000").Select
Selection.Copy
Range("AA7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet1").Select
Range("E7").Select
Sheets("Sheet1").Visible = xlHidden

End Sub

 

[Zot]

I have created a sheet with mock up data consists of alphabets.
Objective: To find column in row 4 that contains X.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	A	C	V	B	N	C	X	V	B
3	S	D	F	G	H	J	K	L
4	A	F	C	D	E	X	G	H	I	J	K
5	D	G	H	J	K	Q	W	X	R	T	Y
6	C	H	F	G	L	M	B		T
7
8
Sheet1

Below is an example of code to find X. strCol will give a column letter of F as answer.

Sub FindCol()

Dim strCol As String
Dim rngFound As Range, rngCol As Range
Dim ws As Worksheet
Dim wb As Workbook

Set wb = ActiveWorkbook

For Each ws In wb.Sheets
    ' Set range of column to search in row 4
    Set rngCol = ws.Range("A4", ws.Cells(4, Columns.Count).End(xlToLeft))
    Set rngFound = rngCol.Find(What:="X", LookAt:=xlWhole)
    If Not rngFound Is Nothing Then
        strCol = Split(rngFound.Address, "$")(1)
    Else
        MsgBox "Nothing is found"
    End If
Next

End Sub

 

[SendHalp3]

Thank you! How would I incorporate this to find the column, and then have that column be copied? would i put rngFound (or rngCol) in that case?

Thank you again and sorry if this is confusing!

 

[Zot]

Thank you! How would I incorporate this to find the column, and then have that column be copied? would i put rngFound (or rngCol) in that case?

Thank you again and sorry if this is confusing!

You mentioned that the specific value you were looking for is in row4. So, in my mock up data, I will look for value in row 4. The line
Set rngCol = ws.Range("A4", ws.Cells(4, Columns.Count).End(xlToLeft))
so rngCol is defined as a range of column to search in row 4 of the ws.

In this example I want to look for X in that rngCol. So the line below is using Find function to look for X in rngCol.
Set rngFound = rngCol.Find(What:="X", LookAt:=xlWhole)

The result rngFound would be the range where X is found. To extract the column alphabet from rngFound address, I uses
strCol = Split(rngFound.Address, "$")(1)

So, the strCol = F for the example I provided.

Note that command rngFound.Address would give $F$4, thus why I use Split function to extract letter F.

The code will loop through each sheets in workbook and look for your specific value in row 4. You just need to replace X with the value you are looking for.