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,

Once copied, where are you pasting the values?

Cheerio,
vcoolio.

 

[Chewyhairball]

Hi. In the same cells. I'm basically removing the formula and replacing with the value instead.
Thankd

 

[My Aswer Is This]

I would try this:
Any time you double click on any cell. That rows data will be converted to values. The formulas will be removed on all cells in that row.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified 6/26/18 7:15 AM EDT
ans = Target.Row
answer = MsgBox("Are you sure", vbYesNo + vbQuestion, "Convert to values")
If answer = vbYes Then
Rows(ans).Value = Rows(ans).Value
End If
End Sub

 

[vcoolio]

......or this placed in the Worbook module:-

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

lCol = Sh.Cells(1, Columns.Count).End(xlToLeft).Column

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

To implement this code:-

- Right click on the Sheet1 tab.
- Select "View Code" from the menu that appears.
- In the big white field that then appears, paste the above code.

The code will then work in any sheet. Type the word "Complete" in a cell in the last column in any sheet then click away (or press enter or down arrow) and the code will execute, removing the formulae in the relevant row.

I hope that this helps.

Cheerio,
vcoolio.

 

[vcoolio]

Sorry Rory,

I've just realised that I've left out two lines of code (in red font below) that are needed. It should be 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

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

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

BTW, the code is not case sensitive so you can type in "Complete" or "complete" or" COMPLETE" etc. and it will still work.

Cheerio,
vcoolio.

 

[Chewyhairball]

Sorry Rory,

I've just realised that I've left out two lines of code (in red font below) that are needed. It should be 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

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

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

BTW, the code is not case sensitive so you can type in "Complete" or "complete" or" COMPLETE" etc. and it will still work.

Cheerio,
vcoolio.

Hi

Thanks for your help with this. It doesnt seem to do anything however??. Nothing happens when I type complete and hit return?

 

[vcoolio]

Hello Rory,

Not sure why it doesn't work for you however I did notice that there's one step I missed in the implementation of the code which could be the reason.
With the implementation instructions, after you have selected "View Code", double click on ThisWorkbook (over to the left in the Project Explorer) and then paste the code into the white code field.

Try that and let us know if that works.

Cheerio,
vcoolio.

 

[Chewyhairball]

Hi

I have copied and pasted the code into ThisWorkbook.

for example i type 'complete' into cell AH455 then press return...nothing happens?

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Double click on ThisWorkbook this way it will work on all sheets in your Workbook
Paste the code in the VBA edit window

Option Compare Text
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Modified 6/26/18 9:40 PM EDT
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastcolumn As Long
Dim ans As Long
ans = Target.Row
Lastcolumn = Cells(ans, Columns.Count).End(xlToLeft).Column
If Target.Value = "complete" Then Rows(ans).Value = Rows(ans).Value
End Sub