Fundemental Change in Thinking to Select a Column to Copy and Paste

[Guzzlr]

Range("C4:C5000").Copy
Range("G4:G5000").Select
Selection.PasteSpecial Paste:=xlPasteValues

Hello All...
I use the term Fundamental in the title, because I've always used a Column number to find the column I wanted to work on.
In the above code, I use Column C because the heading of C4 is the data I wish to copy and move.
Instead, is there a way to find the heading word in any column to choose that column to work with.
For example, what if C4 has a title/heading of "Zone". Instead of counting over or using C4, can I have excel search for the term "Zone", regardless of column number, and excel will "know" that is the column I wish to work on?
I've never done it this way, or if it is possible.
Thanks for the help

 

[Fluff]

How about

Sub FndCopy()

   Dim Fnd As Range
   
   Set Fnd = Range("4:4").find("Zone", , , xlWhole, , , False, , False)
   If Fnd Is Nothing Then Exit Sub
   Intersect(Range("4:5000"), Fnd.EntireColumn).Copy
End Sub

 

[Norie]

You could use Application.Match to get the column number.

Res = Application.Match("Zone", Rows(4), 0)

If Not IsError(Res) Then
    Intersect(Columns(Res), Range("4:5000")).Copy
    Range("G4").PasteSpecial xlPasteValues
End If

 

[Guzzlr]

How about

Sub FndCopy()

   Dim Fnd As Range
   
   Set Fnd = Range("4:4").find("Zone", , , xlWhole, , , False, , False)
   If Fnd Is Nothing Then Exit Sub
   Intersect(Range("4:5000"), Fnd.EntireColumn).Copy
End Sub

Two questions about this code:
1. Why all the comma's?
2. Instead of Exiting the Sub, can we use resume, so the programs keeps running?
thanks

 

[Fluff]

The commas are the to save typing out the name of the parameters. Have a look here for details https://excelmacromastery.com/excel-vba-find/

I don't quite follow what you mean in question 2.

 

[Guzzlr]

The commas are the to save typing out the name of the parameters. Have a look here for details https://excelmacromastery.com/excel-vba-find/

I don't quite follow what you mean in question 2.

It looks like if the word Zone is not in row 4, then the entire program ends. How can it keep moving if Zone is not in row 4?
Also, I'll need to find/search for more heading in row 4 in addition to the word Zone. Can I use the same dimension of Fnd with an "And" statement.
For example:

   Dim Fnd As Range
   
   Set Fnd = Range("4:4").find("Zone", , , xlWhole, , , False, , False) And Range("4:4").find("Bureau", , , xlWhole, , , False, , False)
   If Fnd Is Nothing Then Resume
   Intersect(Range("4:5000"), Fnd.EntireColumn).Copy
End Sub

Thanks

 

[Fluff]

What do you want to do if the word is not found?

 

[Guzzlr]

What do you want to do if the word is not found?

Continue with the rest of the code in the program.
Could I not just use the statement of On Error Resume Next?
Thanks

 

[Fluff]

This will copy the column if found & then continue

Sub FndCopy()

   Dim Fnd As Range
   
   Set Fnd = Range("4:4").find("Zone", , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then
      Intersect(Range("4:5000"), Fnd.EntireColumn).Copy
      Range("G4").PasteSpecial Paste:=xlPasteValues
   End If
End Sub

 

[Guzzlr]

This will copy the column if found & then continue

Sub FndCopy()

   Dim Fnd As Range
   
   Set Fnd = Range("4:4").find("Zone", , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then
      Intersect(Range("4:5000"), Fnd.EntireColumn).Copy
      Range("G4").PasteSpecial Paste:=xlPasteValues
   End If
End Sub

OK..This is working nicely...Thank you
Instead of Copy the column, what if I wanted to cut and paste, instead of copy and paste?
I tried using Cut instead of Copy, and it faulted on me
Thanks