Find content in 1st row and copy column

DrH100

Board Regular
Joined
Dec 30, 2011
Messages
78
Hi

I’ve been using the following code to search through anworksheet, find the contents of Sheet2 a5 and copy the entire column to adifferent sheet.


HTML:
Columns(Sheets("Sheet1").UsedRange.Find(Sheets("Sheet2").Range("a5").Value,LookIn:=xlValues, lookat:=xlWhole).Column).EntireColumn.Copy

Sheets("Pilots").Range("n1").PasteSpecialxlAll


I’m sure it could be tidier but it has done exactly what Ineeded it to do as the contents of Sheet2 A5 have always been unique and notappeared anywhere else in the worksheet.
I now want to search for Sheet2 A6 and do the same (copy thecontents of the column headed up by that value) but the value is going toappear throughout the worksheet. I’vetried substituting the cell ref and it copies the first entry on the sheet itgets to.

How can I either amend my original code or use new to justsearch through the 1st row where the contents will only appear onceand then copy the column beneath it?
Many thanks in advance
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Maybe...

Code:
Sub xxx()
Sheets("Sheet1").Rows(1).Find(Sheets("Sheet2").Range("a6").Value, after:=Sheets("Sheet1").Cells(1, Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column), LookIn:=xlValues, lookat:=xlWhole).EntireColumn.Copy Sheets("Pilots").Range("n1")
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

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