Page 1 of 2 12 LastLast
Results 1 to 10 of 18

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

  1. #1
    New Member
    Join Date
    Apr 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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 Name First Name Rank Unit Member Sig. Unit Com. Sig. Group Com. Sig MSG/Wing Com. Sig SVS Approval Approval Date Expiration Date Check Out Date Fiscal Year
    Smith John Pvt. 199 X X X X X 4/1/2019 5/16/2019 4/22/2019 2019

  2. #2
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,243
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    5 Thread(s)

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

    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?
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    New Member
    Join Date
    Apr 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    Column L is entered Manually

  4. #4
    New Member
    Join Date
    Apr 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

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

  5. #5
    New Member
    Join Date
    Apr 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  6. #6
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,810
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

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

    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?

    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  7. #7
    New Member
    Join Date
    Apr 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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 by TimPin; Apr 30th, 2019 at 04:42 PM.

  8. #8
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,243
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    5 Thread(s)

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

    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
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  9. #9
    New Member
    Join Date
    Apr 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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?

  10. #10
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,243
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    5 Thread(s)

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

    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 by mumps; May 1st, 2019 at 10:56 AM.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •