VBA Copy & Paste values based on adjacent cell

[phils]

Hi all,

Any help would be greatly appreciated.

I have a spreadsheet I use to document tasks, I use column B to give each task (row) a numeric number which only appears should I have a task heading entered in the adjacent column C row. I use as if formula in column B. Ideally I would like to change this formula to a numeric value on saving (or closing the workbook) should the relevant row in column C contain a value. I don't know if it possible to run a VBA like this. If this isn't possible I will use a short cut key or command button

I have managed to get something working for 1 row as below but I would like to extend this so it checks all rows automatically.

My VBA code is as below:

If Not IsEmpty(Range("C4").Value) = True Then

Worksheets("TASK SHEET").Range("B4").Copy


Worksheets("TASK SHEET").Range("B4").PasteSpecial Paste:=xlPasteValues


Application.CutCopyMode = False

End If

End Sub

I hope I have been clear with my predicament

Many thanks for reading

Phil

 

[MrTeeny]

Not exactly sure what you're looking for but here's a few ideas that may help

Excel has a function Workbook_BeforeClose which will run prior to the workbook closing, we can use that to save automatically and also run any VBA code prior to saving. You'd simply place the code in the VBA editor under the ThisWorkbook object. There's also Workbook_BeforeSave function that may be more relevant.

Private Sub Workbook_BeforeClose(Cancel As Boolean)  'Save changes to workbook when closing, without prompting user.
  Me.Save
End Sub

To change a cell from a formula to it's value is as simple as

Worksheets("TASK SHEET").Range("B4").Value = Worksheets("TASK SHEET").Range("B4").Value

It's simply taking the value the cell holds and putting it back into the cell as the value only, if we wanted the formula we'd used .Formula not .Value


If you want to loop thru a range of cells you can use a simple For, next loop. To find the last cell in the range we use FinalRow = Cells(Rows.count, "C").End(xlUp).Row , I'll loop starting at C4 but obviously change the starting Cell from 4 if you need to.

FinalRow = Cells(Rows.count, "C").End(xlUp).Row

    For i = 4 To FinalRow
     
    If Worksheets("TASK SHEET").Range("C" & i ).Value<>"" Then Worksheets("TASK SHEET").Range("B" & i").Value = Worksheets("TASK SHEET").Range("B" & i).Value

    Next i

Sticking it all together would be something like

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error GoTo xit:
Application.ScreenUpdating = False
Application.Calculation = xlCalculateManual
Dim FinalRow As Integer
  
  FinalRow = Cells(Rows.Count, "C").End(xlUp).Row


    For i = 4 To FinalRow
     
    If Worksheets("TASK SHEET").Range("C" & i).Value <> "" Then Worksheets("TASK SHEET").Range("B" & i).Value = Worksheets("TASK SHEET").Range("B" & i).Value


    Next i
  
  Me.Save




xit:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub