VBA Code to paste special values over a cell depending on another cells value....

[LPS ESQ]

Hello there,

Sorry been a very long time since I visited this site. Apologies.

I am trying to do something relatively simple (I think) in Excel. (Excel 365 Version 2302 Build 16.0.16130.20586) 64 bit.


Basically I have a range of cells say K4:Z4. Each cell has a formula of =if(K$3<$a$2,"yes","No"), so for M4 would be M3 etc etc...

Row 8 for each column has a =NOW() as a timestamp.

What happens is that each time data is entered into rows 24 onwards, when complete and in the correct format column(whatever) row 4 changes from "no" to "yes" based upon formulae.

What I want to happen, and this is where I am struggling - it's probably so obvious that I cannot see it is upon saving a macro runs so that every row 8, column whatever cell, where row 4 is "yes" overwrites the =NOW() with copy paste special values & formats.

The code I have so far is this: (Yes I am a few years out of VBA coding so it is a tad bodged! - so much so, it doesn't work.) Any help so that it checks K4 and if "YES", copy paste special value of K8 into K8 then moves onto L4 etc etc... )

Many thanks in advance.









Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Sheet1.Select
Range("E6").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False


'Option Explicit

'Sub APM()

Sheet1.Select
Dim i As Long
For i = 11 To 500
If Cells(i, 4) = "Yes" Then
Cells(i, 8).Copy
Cells(i, 8).PasteSpecial xlPasteValues
Exit For
Next i
'End If

End If
Call GetUserName_Environ
End Sub





Sub GetUserName_Environ()

Dim x As Integer

x = Sheet1.Range("A1").Value

For idx = 1 To 255
strEnvironVal = VBA.Interaction.Environ$(idx)
ThisWorkbook.Sheets("DATA").Cells(idx + 5, x) = strEnvironVal
Next idx

End Sub

 

[Micron]

Maybe it's just that you appear to have your row,column references backwards? Stepping through your code and checking your variables and reference values will often reveal what the issues are.
BTW, if you post code please do so within code tags (use vba button on posting toolbar) to maintain indentation and readability. Also, you don't have to select and copy. It is often enough to just make Sheets("Sheet1).Range("B2") = Sheets("Sheet2).Range("A1") as a rough example.