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

TimPin

New Member
Joined
Apr 23, 2019
Messages
20
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
Last NameFirst NameRankUnitMember Sig.Unit Com. Sig.Group Com. SigMSG/Wing Com. SigSVS ApprovalApproval DateExpiration DateCheck Out DateFiscal Year
SmithJohnPvt.199XXXXX4/1/20195/16/20194/22/20192019

<tbody>
</tbody>
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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?
 
Upvote 0
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.
 
Upvote 0
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>
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.

Code:
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
 
Upvote 0
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?
 
Upvote 0
Assign this macro to your button:
Code:
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?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top