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

SendHalp3

New Member
Joined
Oct 2, 2021
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
I have created a sheet with mock up data consists of alphabets.
Objective: To find column in row 4 that contains X.

Book1
ABCDEFGHIJKL
1
2ACVBNCXVB
3SDFGHJKL
4AFCDEXGHIJK
5DGHJKQWXRTY
6CHFGLMBT
7
8
Sheet1


Below is an example of code to find X. strCol will give a column letter of F as answer.
VBA Code:
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
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,860
Messages
6,181,440
Members
453,042
Latest member
AbdelrahmanExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top