Copy and Paste Values

[Chewyhairball]

Hi Folks

I can't figure this out at all.

I have a table of data. They are all formula links to various other sheets.

Once each row has been checked the last column in the row has the text 'complete' typed into it.
I then select the row and copy/paste values to remove the formulas.

It would be good if there was some way of automatically doing this whenever ' complete' is entered

The only thing i have managed to do is to select a cell in the last column when' complete' is entered but the cell selected is the cell below it when i hit enter after typing 'complete'...i gave up trying to code the rest after that


One last thing. I have 20+ tabs each containing data tables so the last column is not always the same so it would be useful if any code could find the last column rather than me typing a specific range.

hope this makes sense

thanks

Rory

 

[vcoolio]

Hello Rory,

I've run the code a number of times in a little sample workbook that I created and it works just fine. I even tested M.A.I.T's last code in the same sample and it too works fine.

The only thing that I can suggest is to alter my code slightly as follows:-

Option Compare Text

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

        Dim lCol As Long
        Dim c As Range

Application.ScreenUpdating = False

[COLOR=#ff0000]lCol = Sh.Cells(Target.Row, Columns.Count).End(xlToLeft).Column[/COLOR]
If Target.Column <> lCol Then Exit Sub
If Target.Count > 1 Then Exit Sub

On Error Resume Next
For Each c In Sh.Cells.SpecialCells(xlCellTypeFormulas)
      If Sh.Cells(c.Row, lCol) = "Complete" Then
      Sh.Cells(c.Row, c.Column) = c.Value
      End If
Next c

Application.ScreenUpdating = True

End Sub

The line in red font has the minor alteration.

Test it in a copy of your work book and let us know how it works out.

Cheerio,
vcoolio.

 

[Chewyhairball]

Hi

It is a marco enabled woksheet yes. I assumed it had to be to run macros.

Thats works a treat Thank you.

In my last code I had a bit that also replaced any blanks cells with a dash(-)

Selection.Replace What:="", Replacement:="-", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Application.CutCopyMode = False

Is it possbile to have something similar added to this code?

Some of the formulas pulled through results and if there was no result it left the cell blank.
If the cell is blank I would like a -.

This only worked for me before after the formulas had been removed.

I really appreciate your help with this

Rory

 

[My Aswer Is This]

Which script are you saying worked for you?
Two of us have provided code.
Do you want this added feature added to my script or did you even use it.

 

[Chewyhairball]

Hi guys

MASSVIVE appologies to you both..I didn’t even notice the replies were from two different people (I must be losing it)

Vcoolio - the code doesn’t seem to work for me at all. thanks for trying to help me though

‘My answer is this - your code works lovely ��. and it I could get the dashes it would be great.
The code works if I type “complete” or if I use a drop down box which is great. I thought I would try and automate things further by using a simple if statement like if x,y,z = something then “complete” but it does not seem to work if a formula is used to generate the text. It’s not essential but would be good.

 

[My Aswer Is This]

Do you mean if last column value= complete or Apple or Cake

 

[My Aswer Is This]

This will provide for your last request about putting - in empty cells.
Still not sure what you mean about if x or y or z

Option Compare Text
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Modified 6/27/18 5:40 AM EDT
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastcolumn As Long
Dim ans As Long
Dim c As Range
ans = Target.Row
Lastcolumn = Cells(ans, Columns.Count).End(xlToLeft).Column
If Target.Value = "complete" Then
Rows(ans).Value = Rows(ans).Value
For Each c In Range(Cells(ans, 1), Cells(ans, Lastcolumn))
If c.Value = "" Then c.Value = "-"
Next
End If
End Sub

 

[Chewyhairball]

Yes. Just now I type in complete using your code and it works. If I use a formula in the same cell to generate the text " complete" it doesn't work. It seems to only work if I hand type and hit return. thanks

 

[My Aswer Is This]

That is correct. The script only runs when you manually enter a value in the cell.
The script does not work when a formula enters complete into a cell.

 

[My Aswer Is This]

A lot of work done here to now realize you wanted all this to happen when a formula caused completed to be entered.

So you want a formula to cause all cells with formulas to erase the formulas.

 

[Chewyhairball]

Hi. A lot of work yes and very much appreciated. I have what I asked for so don't worry about the formula thing. I didnt ask before as I didn't realise it mattered and I could just add the formula myself. It's cool though, it's not needed. Thanks for all your help, it's certainly made my life easier. ??