Need help with moving rows to another workbook

jdr360

New Member
Joined
Nov 12, 2017
Messages
18
Hi, this one is completely beyond my level of VBA experience. I'm hoping someone can help with this. I have a rather complex Userform for easy data entry into a Excel Database. I now need help with the following, when the user clicks a Purge button, it looks for data in two columns (32 and 34). If they match "Y", then copy those rows to another workbook, I found out how to find the rows correctly, but the next part I'm not sure how to do, need help with the following:

1. In the Database, I need to find all the dates in Column 2 with the following Finance Periods:
- Dec 24 - Jan 06
- Jan 07 - Jan 20
- Jan 21 - Feb 03
...continues for the whole year

2. Then move the rows within the Finance Periods to another Workbook, in a Folder (\Ready for Finance\) Named with the years (2017 - 2018)

3. Put each row into worksheets named with period dates:
- Dec 24 - Jan 06
- Jan 07 - Jan 20
- Jan 21 - Feb 03
...and continues for the whole year

4. The Date format needs to be dd MMM yyyy

This is what I have so far:

Code:
Private Sub cmdPurge_Click()
    
    Dim LastRow As Integer, i As Integer, erow As Integer
    
    If MsgBox("This will purge ALL records showing Ready for Finance, Are you sure?", vbQuestion + vbYesNo) = vbYes Then

        LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row


        For i = 2 To LastRow


        If Cells(i, 32) = "Y" And Cells(i, 34) = "Y" Then
            Range(Cells(i, 1), Cells(i, 34)).Select
            Selection.Copy
            Workbooks.Open Filename:="\Ready for Finance\2018-2019.xlsx"
            Worksheets("Sheet1").Select
            erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
            ActiveSheet.Cells(erow, 1).Select
            ActiveSheet.Paste
            ActiveWorkbook.Save
            ActiveWorkbook.Close
            Application.CutCopyMode = False
        End If
        Next i

        MsgBox ("Records purged.")
    Else
        MsgBox ("No records.")
    End If


End Sub

Like I said, this one is rather complex for my experience level and any help is greatly appreciated.
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Watch MrExcel Video

Forum statistics

Threads
1,123,307
Messages
5,600,869
Members
414,411
Latest member
Snowmanaus

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
Top