VBA Cut and Paste Data From One Sheet to appropriate Sheet based on FY Date

[TimPin]

Hello, I am hoping someone can help me with my dilemma, I don't even know where to begin coding this one.

I have a Workbook with 6 Sheets: TRACKER, FY19 SUMMARY, FY20 SUMMARY, FY21 SUMMARY, FY22 SUMMARY, FY23SUMMARY, and FY24 SUMMARY.

I need to move a portion of a row of data from the TRACKER worksheet to the appropriate FY SUMMARY worksheet based on Fiscal Year. There is a macro to place a Check Mark Symbol in columns E-I when clicked and a formula to calculate Expiration Date in Column K and Fiscal Year in Column M.

I need to append the cells in Columns A-L after the last row of data in appropriate Fiscal year worksheet when Column L is populated, I don't want any formatting or formulas to be copied, only the contents.

Each of the columns A-M have a header in Row 1 and each of the FY SUMMARY sheets have the same headers for rows A-L (Fiscal Year not copied).

I am going to place a Macro Button in the spreadsheet to perform the operation.

Any help would be greatly appreciated.

My TRACKER Sheet looks like this.

A B C D E F G H I J K L M
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]Rank[/TD]
[TD]Unit[/TD]
[TD]Member Sig.[/TD]
[TD]Unit Com. Sig.[/TD]
[TD]Group Com. Sig[/TD]
[TD]MSG/Wing Com. Sig[/TD]
[TD]SVS Approval[/TD]
[TD]Approval Date[/TD]
[TD]Expiration Date[/TD]
[TD]Check Out Date[/TD]
[TD]Fiscal Year[/TD]
[/TR]
[TR]
[TD]Smith[/TD]
[TD]John[/TD]
[TD]Pvt.[/TD]
[TD]199[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]X[/TD]
[TD]4/1/2019[/TD]
[TD]5/16/2019[/TD]
[TD]4/22/2019[/TD]
[TD]2019[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[mumps]

Is column L entered manually or is it the result of a formula? Do you want to copy A to L one row at a time as column L is populated?

 

[TimPin]

Column L is entered Manually

 

[TimPin]

Column L is entered manually. Thank you for your help!

 

[TimPin]

I want to add a macro button to move all rows that have cell L populated to the appropriate sheet when the button is clicked.

 

[My Aswer Is This]

You said:

to the appropriate sheet
How do we know what appropriate sheet is?
And in post 1 you said:

I need to move a portion of a row of data from the TRACKER

What portion of the row?​

_{^{<strike>
</strike>}}​

 

[TimPin]

Appropriate meaning Fiscal Year 2019 (Column M) to FY19 SUMMARY, Fiscal Year 2020 to FY20 SUMMARY and so on. I defined the cells in my original statement as Columns A-L.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your "TRACKER" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. The macro will be triggered automatically when you enter a value in any cell in column L and press the RETURN key. You must make sure that all the other columns are filled in first before you make an entry in column L.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("L:L")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim desWS As Worksheet
    Set desWS = Sheets("FY" & Right(Target.Offset(0, 1), 2) & " SUMMARY")
    Range("A" & Target.Row).Resize(, 12).Copy
    desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[TimPin]

Thank you very much for all of your work I do appreciate it, but as I had stated in my OP, I need this to work only when the Macro Button is clicked. This data will only be transferred on a weekly basis, once the 'TRACKER' sheet has been reviewed by a supervisor. If I paste this code to the Macro Button code sheet, will it work that way?

 

[mumps]

Assign this macro to your button:

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim desWS As Worksheet, rng As Range, LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each rng In Range("L2:L" & LastRow)
        Set desWS = Sheets("FY" & Right(rng.Offset(0, 1), 2) & " SUMMARY")
        Range("A" & rng.Row).Resize(, 12).Copy
        desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    Next rng
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

You may have to change the macro name to a button_click event. What is the name of your button?