Page 2 of 2 FirstFirst 12
Results 11 to 18 of 18

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

  1. #11
    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

    My button name is Archive. Thanks again!

  2. #12
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,249
    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

    Try running the macro I posted in Post# 10 manually without using the button. You can do that by pressing the F5 key in the Visual Basic Editor. If it still doesn't work, perhaps you could 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. If the workbook contains confidential information, you could replace it with generic data.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #13
    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

    My Excel File is located Here. https://www.dropbox.com/s/2d8mlvhsm5...v1_4.xlsm?dl=0


    Thank You!

  4. #14
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,249
    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

    Try:
    Code:
    Private Sub CommandButton1_Click()
        Application.ScreenUpdating = False
        Dim desWS As Worksheet, rng As Range, LastRow As Long
        LastRow = Range("A" & Rows.Count).End(xlUp).Row
        For Each rng In Range("L2:L" & LastRow)
            If rng <> "" Then
                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
            End If
        Next rng
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
    End Sub
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  5. #15
    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

    That works great with one exception. I need it to delete the data on the TRACKER after it has been moved.

  6. #16
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,249
    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

    Try:
    Code:
    Private Sub CommandButton1_Click()
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Dim desWS As Worksheet, x As Long, LastRow As Long
        LastRow = Range("A" & Rows.Count).End(xlUp).Row
        For x = LastRow To 2 Step -1
            If Cells(x, 12) <> "" Then
                Set desWS = Sheets("FY" & Right(Cells(x, 13), 2) & " SUMMARY")
                Range("A" & x).Resize(, 12).Copy
                desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
                ActiveSheet.Range("A" & x).Resize(, 13).Delete
            End If
        Next x
        Application.CutCopyMode = False
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End Sub
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  7. #17
    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

    That is AWESOME, works exactly as I wanted. Thank you VERY much!!!

  8. #18
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,249
    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

    You're welcome.
    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
  •