Copy and paste range of data to next available row on different sheet

[Trae1170]

Hello everyone, I have a problem that hopefully one of you smart people can help me with.
I have a spreadsheet that is used for a "Action Tracker" (1st screenshot). In column F, if I choose from the dropdown and place a checkmark in the box, I would like the data in cells G, H, I and J to be copied and pasted into the next available row on a different sheet (screenshot 2) starting on A19, H19, J19 & L19.

Right now I have it directly transferring using a formula which works but if I have any rows on the first sheet that are not checked, then I have a gap in the data on the second sheet. I want VBA code so that whenever a checkmark is placed the data transfers to the next available row so there is no gaps.

The 2nd part, maybe not as easy would be if I "Unchecked" a box on the first sheet it would remove the data from the second sheet and automatically shift all of the data up to the next available row. This would be to continue to prevent any empty rows on the second sheet.

I am using Office360, I can provide the workbook if needed.

 

[NoSparks]

Please use the forum and not PMs, thanks.

I cannot seem to get it to function properly

What is it doing that you don't expect and/or what isn't it doing that you do expect ?

 

[Trae1170]

I am not sure how to put all of your code together or where to put it? Does all of the code from your posts on July 3rd and 5th all go into one string? I did change the worksheet "Change Event" as you stated above.

 

[NoSparks]

After posting that code (a month ago) I was playing with your file and realized the position of the "YES" selection would disrupt the flow of things but with no response I abandoned this.

So after down loading your file again and re-visiting have changed things a bit.
What I have now is

on the sheet module:
(get there by right clicking the sheet tab and selecting view code)

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wsAction As Worksheet, wsSummary As Worksheet
    Dim nextRow As Long, actionRow As Long, i As Long, fndAction As Range
        
    Set wsAction = ThisWorkbook.Sheets("Action Item Tracker")
    Set wsSummary = ThisWorkbook.Sheets("Summary Report")

    ' limit to a single cell
    If Target.CountLarge > 1 Then Exit Sub
    ' in col F, rows 4 thru 497
    If Target.Column = 6 And Target.Row >= 4 And Target.Row <= 497 Then
        actionNum = Range("A" & Target.Row).Value   ' record identity

        If Target.Value = "YES" Then
            With wsSummary
                nextRow = .Range("M35").End(xlUp).Row + 1
                If nextRow < 19 Then nextRow = 19 ' Ensure we start at least at row 19
                If nextRow = 35 Then
                    MsgBox "The Report Area is full" & vbCrLf & "Will now exit sub"
                    Exit Sub
                End If
                .Range("M" & nextRow).Value = actionNum
            End With
        Else
            With wsSummary
                Set fndAction = .Range("M19:M34").Find(What:=actionNum, LookIn:=xlValues, Lookat:=xlWhole)
                If Not fndAction Is Nothing Then
                    .Range("M" & fndAction.Row).ClearContents
                End If
                
                ' code required here to eliminate blank rows from row 19 thru 34
                Call RemoveBlankRows
                
            End With
        End If
    End If
    
Application.EnableEvents = False
  ThisWorkbook.RefreshAll
Application.EnableEvents = True

End Sub

On a standard module:
(from the VBA menu, Insert -> module)

Sub RemoveBlankRows()

    Dim DataRng As Range, arr As Variant
    Dim Ct As Long, i As Long
    Dim DataSH As Worksheet, DataTarget As Range
    
Set DataSH = Sheets("Summary Report")
Set DataRng = DataSH.Range("M19:M34")

ReDim arr(1 To DataRng.Rows.Count, 1)
Debug.Print LBound(arr), UBound(arr)

For i = 1 To DataRng.Rows.Count
    If DataRng.Cells(i, 1) <> "" Then
        Ct = Ct + 1
        Debug.Print Application.Index(DataRng, i, 0).Value
        arr(Ct, 1) = Application.Index(DataRng, i, 0).Value
    End If
Next i

' now have non-blanks in arr, write them to sheet

If Ct > 0 Then
    DataRng.ClearContents
    For i = 1 To Ct
        Set DataTarget = DataSH.Range("M19").Offset(i - 1, 0)
        DataTarget.Value = arr(i, 1)
    Next i
End If

End Sub

Then replaced your formulas in Summary Report cells A19 through L34
with Vlookups back to the Action Item Tracker sheet based on the Action # that was written to column M.

Do you have any objection to me supplying a link to my version of your file here for anybody to see?

 

[Trae1170]

Yes, please share the file. Thank you,

 

[NoSparks]

Here is my version of your file for downloading

 

[Trae1170]

I just tried your version and the data transfer to the summary page does not seem to work.
Only the first and 5th lines on the Action Item Tracker sheet will transfer over and if I uncheck the box, it does not remove the data and move the lines below it up to fill the blank row?

 

[NoSparks]

The picture from May --> has add activeX buttons
The picture in OP here --> indicates you have check marks
The file you linked me to --> has a data validation drop down
Now you say

if I uncheck the box

Doesn't sound like the file I linked you to.

 

[Trae1170]

I am sorry, I removed the ActiveX control buttons in Column Hand just replaced with a dropdown menu with the same 2 options. I would assume that caused the 3 cells to report "0".
Did it also cause the issue with the data not being removed from Summary Report tab when the "YES" is removed from column F on the Action Item Tracker tab? Also the data not shifting up to fill the empty row?

Where is the Vlookup data located? Is there a hidden tab?

Sorry for the confusion

 

[NoSparks]

Please, for the time being, ignore everything you have and let's only deal with the workbook I linked to in post 15.
It is the workbook you provided and is the only real thing I have to deal with.

As provided the workbook would not run due to ThisWorkbook Sub Workbook_Open()
calling a macro that refers to a sheet that doesn't exist in this workbook.
This has been commented out.

Action Item Tracker must have unique Action # in Column A.
I just used numbers. This is the only thing there is to identify the row of data
and is written to column M on Summary Report to know which row is which.

Vlookup are formulas in cells A19 thru L34 of the Summary Report sheet.
The zero's are a result of the lookup cell being blank on the Action Item Tracker sheet.

 

[Trae1170]

I am confused why the data that transferred is out of order on the Summary sheet? I also chose "YES" on an additional line item (#3) on the tracker thinking it would transfer but it didn't?
When I change the "YES" to blank on the tracker, it does not remove the data from the summary page?