Suggest: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" in Column A) to :
Column A beginning with Row 10 in the Sheet named Variance in the 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

--
Ian
--
"STEVEB" <[email protected]> wrote in
message news:[email protected]...
>
> 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" in
> Column A) to :
> Column A beginning with Row 10 in the Sheet named Variance in the
> workbook.
>
> Any help would be greatly appreciated!
>
>
> --
> STEVEB
> ------------------------------------------------------------------------
> STEVEB's Profile:
> http://www.excelforum.com/member.php...fo&userid=1872
> View this thread: http://www.excelforum.com/showthread...hreadid=506383
>

[STEVEB]

Thanks Ian, I appreciate your help!

The code works except, instead of the value of Column C overwiting the Value in Column A, I would like the value in Column C copied to a different sheet in the workbook. I would like the Value of column C copied to a sheet named "variance" in the workbook beginning with Row 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.

--
Ian
--
"Ian" <[email protected]> wrote in message
news:[email protected]...
> For r = 10 To 1000
> If Cells(r, 1).Value = "Variance" Then
> Cells(r, 1).Value = Cells(r, 3).Value
> End If
> Next r
>
> --
> Ian
> --
> "STEVEB" <[email protected]> wrote in
> message news:[email protected]...
>>
>> 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" in
>> Column A) to :
>> Column A beginning with Row 10 in the Sheet named Variance in the
>> workbook.
>>
>> Any help would be greatly appreciated!
>>
>>
>> --
>> STEVEB
>> ------------------------------------------------------------------------
>> STEVEB's Profile:
>> http://www.excelforum.com/member.php...fo&userid=1872
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=506383
>>
>
>

[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 all 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 workbook and if column A (in any sheet) has "variance" then copy the amount in column C (the same row as the text varaince- Colum A) to:

The first sheet in the workbook named variance. The amounts should be 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.

--
Ian
--
"STEVEB" <[email protected]> wrote in
message news:[email protected]...
>
> 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 all
> 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 workbook
> and if column A (in any sheet) has "variance" then copy the amount in
> column C (the same row as the text varaince- Colum A) to:
>
> The first sheet in the workbook named variance. The amounts should be
> copied beginning in row 10 column A.
>
> I hope this helps!
>
> Thanks again!
>
>
> --
> STEVEB
> ------------------------------------------------------------------------
> STEVEB's Profile:
> http://www.excelforum.com/member.php...fo&userid=1872
> View this thread: http://www.excelforum.com/showthread...hreadid=506383
>