Using a formula to copy data from a row if a cell has certain data.

[yankeedavee]

Hey all, Im new at this and am not very good with VBA yet, so was hopping for a formula? I can do a VBA if its easier.

What I want to do is find any Cell in coloumn AC that contains the value "1",
Then copy certain cells from that row in sheet "Trafford MC" to "Sheet 2".

I am having no luck. please help.

[Special-K]

Cells can't copy to other cells, you have to use VBA.
You can, however, use formulas to set cell values dependant on other cells.

What "certain cells" do you want to copy?

You might be better off posting a spreadsheet.
Attach a sample spreadsheet with expected results, remove any sensitive data.

[sourabhg98]

I am not getting exactly what you need.
Still check the attached file.
It may help!!

[yankeedavee]

Ok VBA it is, What I want to do is if column AC of Sheet(Trafford MC) has value of 1, I want columns, B,F,H,S and Z all to be copied over to Sheet2. Im just struggling to get it. Ive seen many examples but cant seem transpose it into mine.

[Special-K]

If columns B F H S and Z are blank create a formula in each column

in 'Trafford MC'!B1
=IF('Trafford MC'!AC1=1,'Trafford MC'!B1,"")
in 'Trafford MC'!F1
=IF('Trafford MC'!AC1=1,'Trafford MC'!F1,"")
in 'Trafford MC'!H1
=IF('Trafford MC'!AC1=1,'Trafford MC'!H1,"")
in 'Trafford MC'!S1
=IF('Trafford MC'!AC1=1,'Trafford MC'!S1,"")
in 'Trafford MC'!Z1
=IF('Trafford MC'!AC1=1,'Trafford MC'!Z1,"")

and copy down the columns

[sourabhg98]

Can you please be more specific?

1. Do you only want a particular value to be searched i.e. 1 or there could be several values?
2. Where do you want the values from column B,F,H,S and Z to be pasted in sheet 2?

[Special-K]

Or for VBA try (untested)

Sub K1()
Lastrow = Worksheets("Trafford MC").Cells(Rows.Count, "AC").End(xlUp).Row
For i =1 to Lastrow
If Worksheets("Trafford MC").Cells(i, "AC")=1
Worksheets("Sheet2").Cells(i, 2)=Worksheets("Trafford MC").Cells(i, 2)
Worksheets("Sheet2").Cells(i, 6)=Worksheets("Trafford MC").Cells(i, 6)
Worksheets("Sheet2").Cells(i, 8)=Worksheets("Trafford MC").Cells(i, 8)
Worksheets("Sheet2").Cells(i, 19)=Worksheets("Trafford MC").Cells(i, 19)
Worksheets("Sheet2").Cells(i, 26)=Worksheets("Trafford MC").Cells(i, 26)
Next i
End Sub

[yankeedavee]

Ok sorry here is a quick example of what I want. hmm... I cant seem to upload a example document.. But if you can imagine that Trafford MC is just full of things like company name etc. The value in AC will either be 1 or empty (" "). So if there is a 1 I want it to copy the as mentioned above columns to copy data to Sheet 2. If I can get this file to upload ill send it. As it stand its just constantly spinning.

[yankeedavee]

If Worksheets("Trafford MC").Cells(i, "AC")=1

This one line comes up red. Not sure why.