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

Guzzlr

Well-known Member
Joined
Apr 20, 2009
Messages
930
Office Version
  1. 2016
Platform
  1. Windows
Code:
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
 
Last edited:

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
How about
Code:
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
 
Upvote 0
You could use Application.Match to get the column number.
Code:
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
 
Upvote 0
How about
Code:
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
 
Upvote 0
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:

Code:
   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
 
Upvote 0
What do you want to do if the word is not found?
 
Upvote 0
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
 
Last edited:
Upvote 0
This will copy the column if found & then continue
Code:
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
 
Upvote 0
This will copy the column if found & then continue
Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,212,938
Messages
6,110,788
Members
448,297
Latest member
carmadgar

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