If Statement

[STEVEB]

Does anyone have any suggestions for the following:

Beginning with the third sheet in the workbook I would like:

If any Cell in Column A = "Variance" (for all sheets in workbook
Then:

Copy the Cell in Colum "C" (in the same row as the text "Variance" i
Column A) to :
Column A beginning with Row 10 in the Sheet named Variance in th
workbook.

Any help would be greatly appreciated

 

[Ian]

For r = 10 To 1000
If Cells(r, 1).Value = "Variance" Then
Cells(r, 1).Value = Cells(r, 3).Value
End If
Next r

 

[STEVEB]

Thanks Ian, I appreciate your help!

The code works except, instead of the value of Column C overwiting th
Value in Column A, I would like the value in Column C copied to
different sheet in the workbook. I would like the Value of column
copied to a sheet named "variance" in the workbook beginning with Ro
10 column A.

HTH .

Thanks again

 

[Ian]

For r = 10 To 1000
If Worksheets("Sheet1").Cells(r, 1).Value = "Variance" Then
Worksheets("Variance").Cells(r, 1).Value = Worksheets("Sheet1").Cells(r,
3).Value
End If
Next r
Change Sheet1 to suit your source sheet. It would probably have worked just
adding the Worksheets("Variance") bit, but VBA can get a bit confused if the
wrong sheet is active when you run the code.

 

[STEVEB]

Thanks Ian,

Getting close...I appreciate your help!

My workbook has between 20 & 40 sheets (This changes weekly)

The first sheet is named "variance" and is used as a summary for al
the other sheets in the workbook.

Within all the other sheets, column A may contain the text "variance
(some Sheets have none, other sheets have many).

I would like the code to look through all the sheets in the workboo
and if column A (in any sheet) has "variance" then copy the amount i
column C (the same row as the text varaince- Colum A) to:

The first sheet in the workbook named variance. The amounts should b
copied beginning in row 10 column A.

I hope this helps!

Thanks again

 

[Ian]

Sorry, I'm afraid I've not done much with multiple sheets in the way you
mention.

Hopefully someone else can help you further.