VBA Code to copy data range from one sheet to another based on a changing value.

[uk_2022]

I need help with a VBA code that will enable me to copy data from one sheet to another sheet if the value in a column is "YES".

The value in column K has a formula which changes the value from NO to YES, based on manually entered data within column N or TRUE values in column O & P which are based on the values in other sheets.

I have tried uploading a mini-sheet but unfortunately it just keeps crashing the program.

Totally new to VBA codes.

 

[mumps]

Could you perhaps upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[uk_2022]

Thanks for the reply.

Have uploaded an example of the spreadsheet. Link is Box

In the workbook there are 12 sheets which are updated manually, from which the 'data' sheet will update.

In the 'Data' sheet there are columns which contain IF statements with criteria based on the values in other sheets or columns.

Sheet 1 and Sheet 4 have a column that will update with a TRUE value if the formula conditions are met (Sheet 1 = column V and Sheet 4 =
column P.

Using any TRUE values in those columns, there is formulas in the 'Data' sheet in column O & P which will also return a TRUE value.
Based on the values in O & P, the K column will then return YES or NO. Column N is a manual entry and is there incase a NO to YES change needs
to be made that doesn't come from the Sheet 1 or Sheet 4.

In terms of using the workbook - The white columns are for manually entering values, the pale grey will populate based on formulas and will be seen
by the user, but the dark grey columns will be hidden from view of the user.

I have added the VBA code which has copied over the initial records which had 'YES' in column K. However it doesn't pick up any changes after that.

For example, I ran the VBA code which added the two records with "YES" in column K. I then made a manual change to "Jane Jones" record in the 'Sheet 4' which then returned the TRUE value in column P in the 'Data' sheet, which returned YES in column K.
However, the NO to Yes change didn't then copy the data to the 'EQUAL' sheet.

The trigger change needs to be when the 'Data' column column K changes from NO to YES, but the change won't be manual it will be the result of a formula if the conditions are met.

Can you advise?

The VBA code is in the Data sheet.

If we can solve this issue, I would like to apply a VBA to each sheet, if columns Z:AJ return a TRUE value, it would copy the names from H:I into columns H:I within the specified sheet.

For example;

if 'data' column Z is TRUE, copy H:I to 'Sheet 6' sheet; column H:I

and so on for the other sheets.



TIA

 

[mumps]

I got a message that the file has been removed from Box.com. Try uploading it again and post the link.

 

[uk_2022]

Box

app.box.com

 

[mumps]

To clarify, is the data range copied only to sheet "EQUAL"?

 

[uk_2022]

Yes

 

[mumps]

Try:

Sub CopyData()
    Application.ScreenUpdating = False
    Sheets("EQUAL").Range("C9", Sheets("EQUAL").Range("I" & Rows.Count).End(xlUp)).ClearContents
    With Sheets("Data")
        .Range("A8").AutoFilter 11, "YES"
        .Range("C9", .Range("I" & .Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).Copy Sheets("EQUAL").Range("C9")
        .Range("A8").AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

 

[uk_2022]

Hi

Yes that works, but is there any way that a code can automatically add (when column K changes from NO to YES) any new names to the EQUAL sheet without having to re-run the macro each time?

TIA

 

[uk_2022]

This is a code we had set up; it copied the initial records that had 'YES' in column K, but it only worked if any changes from NO to YES were done manually in column K rather than the result of the formula criteria from columns M:P

Sub InitialLoad()
Dim lrow As Long
Dim llrow As Long
Dim Count As Long

lrow = Range("H" & Rows.Count).End(xlUp).Row
llrow = Worksheets("EQUAL").Range("H" & Rows.Count).End(xlUp).Row
    For Count = 2 To lrow
        If Range("K" & Count) = "YES" Then
                llrow = llrow + 1
                Worksheets("EQUAL").Range("H" & llrow) = Range("H" & Count)
                Worksheets("EQUAL").Range("I" & llrow) = Range("I" & Count)
        End If
        
    Next Count

End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim llrow As Long
    If Not Intersect(Target, Range("K:K")) Is Nothing Then
        If Target = "YES" Then
                llrow = Worksheets("EQUAL").Range("H" & Rows.Count).End(xlUp).Row + 1
                Worksheets("EQUAL").Range("H" & llrow) = Range("H" & Target.Row)
                Worksheets("EQUAL").Range("I" & llrow) = Range("I" & Target.Row)
        End If
    End If
End Sub