Copy Data From One Cell to Another Based on Data in a Third Cell in Multiple Sheets

[slpswhite]

I have a spreadsheet which has 10 worksheets, starting with the third worksheet and all those which follow I need to evaluate the data in column K to see if it contains the following:

Approved CTO or Approved-STM

If column K contains either of these I need to look at the cell in column L and have the numeric value there copied in column M

 

[mumps]

Place this formula in cell M2 and copy it down as far as you need it: =IF(OR(K2="Approved CTO",K2="Approved-STM"),L2,"")

 

[Fluff]

If you want a macro try

Sub CopyValues()

   Dim Cnt As Long
   
   For Cnt = 3 To Worksheets.Count
      With Sheets(Cnt).Range("M2:M" & Sheets(Cnt).Range("K" & Rows.Count).End(xlUp).Row)
         .Value = Evaluate(Replace("IF((@=""Approved CTO"")+(@=""Approved ATM"")," & .Offset(, -1).Address & "," & .Address & ")", "@", .Offset(, -2).Address))
      End With
   Next Cnt
End Sub

With thanks to Rick Rothstein for help with the Evaluate

 

[slpswhite]

Fluff thanks for the help, I choose to use the code version since I already have some going on these spreadsheets. This almost works, I had an issue where it returned a value of 0 for all entries in column M. Once I looked at it I noticed a typo in the code where we had "Approved ATM and it should have been Approved-STM. Once I changed it it copied the values for the first sheet correctly. I can't tell you how many times I hit the blasted A key instead of the S, all the time! The problem seems it is using the entries from sheet one for all the other worksheets so for instance the values on:

Sheet 3 Column L Sheet 3 Expected in M Actual in M

2 2 2
6 6 6
4 4 4

Sheet 4 Column L Sheet 4 Expected in M Actual in M
5 5 2
2 2 6
5 5 4

If there are more entries (Rows) in other sheets it returns a 0 for all those.

I am not sure this all makes much sense if not let me know. The current code is:

Sub CopyValues()
   Dim Cnt As Long
   
   For Cnt = 3 To Worksheets.Count
      With Sheets(Cnt).Range("M2:M" & Sheets(Cnt).Range("K" & Rows.Count).End(xlUp).Row)
         .Value = Evaluate(Replace("IF((@=""Approved CTO"")+(@=""Approved-STM"")," & .Offset(, -1).Address & "," & .Address & ")", "@", .Offset(, -2).Address))
      End With
   Next Cnt
End Sub

 

[Fluff]

Ok, try

Sub CopyValues()
   Dim Cnt As Long
   
   For Cnt = 3 To Worksheets.Count
      Sheets(Cnt).Activate
      With Sheets(Cnt).Range("M2:M" & Sheets(Cnt).Range("K" & Rows.Count).End(xlUp).Row)
         .Value = Evaluate(Replace("IF((@=""Approved CTO"")+(@=""Approved-STM"")," & .Offset(, -1).Address & "," & .Address & ")", "@", .Offset(, -2).Address))
      End With
   Next Cnt
End Sub

 

[slpswhite]

mumps this works great! Thanks it's actually going to help me on another workbook I have too. I am working with the code Fluff replied with as I already have some code going in this workbook.

 

[slpswhite]

As always magic from you! It works perfect!

 

[Fluff]

Glad we could help & thanks for the feedback.

As I said in Post#3 I needed help from Rick Rothstein to get the Evaluate working correctly