Moving rows to another sheet or workbook

J

jdr360

New Member
Joined
Nov 12, 2017
Messages
18
I have been stuck on this for awhile now, need to move rows to another worksheet or workbook. The 1st part should find all rows marked cancelled (Column AG is "Y"). 2nd part should find all rows marked ready (Column AH is "Y"). Code runs, but results are very strange. Does not move all rows, also seams to copy row 1 (headers). This is what I have so far:

Code: 
        If MsgBox("This will purge ALL records showing Cancelled as well as" & vbNewLine & "All records showing Ready for Finance, between:" & vbNewLine & vbNewLine & Format(StartDate, "dd MMM yyyy") & " and " & Format(EndDate, "dd MMM yyyy") & vbNewLine & vbNewLine & "Are you sure?", vbQuestion + vbYesNo) = vbYes Then
            Me.sysMsgBox.Value = " Purging records marked Cancelled..."
            Application.ScreenUpdating = False  '---->Prevents screen flickering as the code executes.
            Application.DisplayAlerts = False  '---->Prevents warning "pop-ups" from appearing.
            '--------------------------------
            ' Look for all Cancelled records
            '--------------------------------
            Range("AG2", Range("AG" & Rows.Count).End(xlUp)).AutoFilter Field:=1, Criteria1:="Y"  'Filters Column AG for "Y"
            UpdateStatus
            CTotal = nCancelledCalls
            Range("A2", Range("AZ" & Rows.Count).End(xlUp)).Copy wsCancelled.Range("A" & Rows.Count).End(xlUp)(2)  ' Copies row data from Columns A - AZ & transfers it to Cancelled into the next available row.
            Range("A2", Range("AZ" & Rows.Count).End(xlUp)).Delete '---->Deletes the data from MRC Database. This also prevents duplicates in Cancelled.
            wsMRC.Activate '----> Takes you back to MRC Database.
            Application.CutCopyMode = False  '---->Prevents the "marching ants" from bordering the copied rows of data.
            If ActiveSheet.AutoFilterMode Then
                If ActiveSheet.FilterMode Then
                    ActiveSheet.ShowAllData
                End If
            ElseIf ActiveSheet.FilterMode Then
                ActiveSheet.ShowAllData
            End If
            '----------------------------------------
            ' Look for all Ready for Finance Records
            '----------------------------------------
            Range("AH2", Range("AH" & Rows.Count).End(xlUp)).AutoFilter Field:=1, Criteria1:="Y"  'Filters Column AH for "Y"
            UpdateStatus
            FTotal = nClosedCalls
            Range("A2", Range("AZ" & Rows.Count).End(xlUp)).Copy wsFinance.Range("A" & Rows.Count).End(xlUp)(2)  ' Copies row data from Columns A - AZ & transfers it to Finance into the next available row.
            Range("A2", Range("AZ" & Rows.Count).End(xlUp)).Delete '---->Deletes the data from MRC Database. This also prevents duplicates in Finance.
            wsMRC.Activate '----> Takes you back to MRC Database.
            Application.CutCopyMode = False  '---->Prevents the "marching ants" from bordering the copied rows of data.
            If ActiveSheet.AutoFilterMode Then
                If ActiveSheet.FilterMode Then
                    ActiveSheet.ShowAllData
                End If
            ElseIf ActiveSheet.FilterMode Then
                ActiveSheet.ShowAllData
            End If
            Total = CTotal + FTotal
            If Total = 0 Then
                Me.sysMsgBox.Value = " Admin: No records found. No records have been purged."
            Else
                Me.sysMsgBox.Value = " Admin: " & Total & " total record(s) purged. " & FTotal & " record(s) marked ready for finace and " & CTotal & " marked cancelled."
            End If
        Else
            Me.sysMsgBox.Value = " Admin: Cancelled. No records have been purged."
            Application.CutCopyMode = False  '---->Prevents the "marching ants" from bordering the copied rows of data.
            Application.DisplayAlerts = True   '---->Resets the default.
            Application.ScreenUpdating = True  '---->Resets the default.
            Exit Sub
        End If
        Application.CutCopyMode = False  '---->Prevents the "marching ants" from bordering the copied rows of data.
        Application.DisplayAlerts = True   '---->Resets the default.
        Application.ScreenUpdating = True  '---->Resets the default.
        UpdateStatus
        UpdateScreen
Like I said, with above code, I am getting very strange results. Anyone able to help with this?
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
Re: Need help moving rows to another sheet or workbook

Well, I have now completely re-wrote the code as follows:

Code: 
        If MsgBox("This will purge ALL records showing Cancelled as well as," & vbNewLine & "all records showing Ready for Finance, between:" & vbNewLine & vbNewLine & Format(StartDate, "dd MMM yyyy") & " and " & Format(EndDate, "dd MMM yyyy") & vbNewLine & vbNewLine & "Are you sure?", vbQuestion + vbYesNo) = vbYes Then
            Me.sysMsgBox.Value = " Purging records marked Cancelled..."
            MRCForm.Caption = "Monitoring and Response Centre [ Proccessing... Ready to Finance records... Please wait... ]"
            Application.ScreenUpdating = False  '---->Prevents screen flickering as the code executes.
            Application.DisplayAlerts = False  '---->Prevents warning "pop-ups" from appearing.
            If FTarget = 1 Then
                If Application.WorksheetFunction.CountA(Worksheets("Finance").UsedRange) = 0 Then FTarget = 0
            End If
            Set xRg = Worksheets("MRC Database").Range("AH2:AH" & Source)
            On Error Resume Next
            Application.ScreenUpdating = False
            For K = 1 To xRg.Count
                If CStr(xRg(K).Value) = "Y" Then
                    If Cells(xRg(K), 2) > Format(StartDate, "dd MMM yyyy") And Cells(xRg(K), 2) < Format(EndDate, "dd MMM yyyy") Then         '<<== Need to find Start and End dates
                        xRg(K).EntireRow.Copy Destination:=Worksheets("Finance").Range("A" & FTarget + 1)
                        xRg(K).EntireRow.Delete
                        FTotal = FTotal + 1
                        If CStr(xRg(K).Value) = "Y" Then
                            K = K - 1
                        End If
                        FTarget = FTarget + 1
                    End If
                End If
            Next
            MRCForm.Caption = "Monitoring and Response Centre [ Proccessing... Cancelled records... Please wait... ]"
            If CTarget = 1 Then
                If Application.WorksheetFunction.CountA(Worksheets("Cancelled").UsedRange) = 0 Then CTarget = 0
            End If
            Set xRg = Worksheets("MRC Database").Range("AG2:AG" & Source)
            On Error Resume Next
            Application.ScreenUpdating = False
            For K = 1 To xRg.Count
                If CStr(xRg(K).Value) = "Y" Then
                    xRg(K).EntireRow.Copy Destination:=Worksheets("Cancelled").Range("A" & CTarget + 1)
                    xRg(K).EntireRow.Delete
                    CTotal = CTotal + 1
                    If CStr(xRg(K).Value) = "Y" Then
                        K = K - 1
                    End If
                    CTarget = CTarget + 1
                End If
            Next
            Application.ScreenUpdating = True
            UpdateStatus
            wsMRC.Select '----> Takes you back to MRC Database.
            Application.CutCopyMode = False  '---->Prevents the "marching ants" from bordering the copied rows of data.
            Total = CTotal + FTotal
            If Total = 0 Then
                Me.sysMsgBox.Value = " Admin: No records found. No records have been purged. "
                MsgBox " Admin: No records found. No records have been purged. "
            Else
                Me.sysMsgBox.Value = " Admin: " & Total & " total record(s) purged. " & FTotal & " record(s) marked ready for finace and " & CTotal & " marked cancelled. "
                MsgBox " Admin: " & Total & " total record(s) purged. " & FTotal & " record(s) marked ready for finace and " & CTotal & " marked cancelled. "
            End If
        Else
            Me.sysMsgBox.Value = " Admin: Cancelled. No records have been purged. "
            MsgBox " Admin: Cancelled. No records have been purged. "
            UpdateStatus
            Exit Sub
        End If

Above code works, not as fast, but at least it works. Next issue I'm having difficulty with, is moving rows between a StartDate and EndDate to a specific sheet or workbook then another StartDate and EndDate range to another sheet or workbook. Example, first range could be say... "Dec 24 - Jan 06", 2nd range could be "Jan 07 - Jan 20", 3rd "Jan 21 - Feb 03" and so on for the entire year, as variables StartDate and EndDate (as these will change). I have attempted where the line shows:
Code: 
If Cells(xRg(K), 2) > Format(StartDate, "dd MMM yyyy") And Cells(xRg(K), 2) < Format(EndDate, "dd MMM yyyy") Then '<<== Need to find Start and End dates

and I have tried:

Code: 
If Cells(xRg(K), 2) > StartDate And Cells(xRg(K), 2) < EndDate Then '<<== Need to find Start and End dates

This is does not work, anyone able to help???
 
Upvote 0
Re: Need help moving rows to another sheet or workbook

Well I have not received any help on this forum with any of my problems. I have completely re-written everything, it's not pretty code, but it works. I can now purge all Cancelled records to a separate workbook and all records marked ready for finance to another workbook, into worksheets depending on date ranges. The following is what I have in case anyone else needs something similar:


Attached to command button I have this:

Code: 
Private Sub cmdPurge_Click()
    
    Application.ScreenUpdating = False
    Dim Total, FTotal, CTotal, pFTotal, cFTotal, nFTotal As Integer
    Dim Source, pFTarget, cFTarget, nFTarget, CTarget, K As Long
    Dim pSheet, cSheet, nSheet As String
    Dim xRg, dRg As Range, xCell As Range
    ' Get current Finance Period
    cStartDate = Sheets("MRC Settings").Cells(2, 2)
    cEndDate = Sheets("MRC Settings").Cells(2, 3)
    pEndDate = cStartDate - 1
    pStartDate = pEndDate - 13
    nStartDate = cEndDate + 1
    nEndDate = nStartDate + 13
    If Format(Now, "dd-MMM-yyyy") >= cStartDate And Format(Now, "dd-MMM-yyyy") <= cEndDate Then
        pEndDate = cStartDate - 1
        pStartDate = cEndDate - 13
        cStartDate = nStartDate
        cEndDate = nEndDate
        nStartDate = cEndDate + 1
        nEndDate = nStartDate + 13
        Sheets("MRC Settings").Cells(2, 2) = cStartDate
        Sheets("MRC Settings").Cells(2, 3) = cEndDate
    End If
    Source = 0
    Total = 0
    FTotal = 0
    pFTotal = 0
    cFTotal = 0
    nFTotal = 0
    CTotal = 0
    Workbooks.Open (ThisWorkbook.Path & "\Data\Cancelled.xlsm") ' Test
    Workbooks.Open (ThisWorkbook.Path & "\Data\Finance.xlsm") ' Test
    Set wsMRC = ThisWorkbook.Worksheets("MRC Database")
    Set wbCancelled = Application.Workbooks("Cancelled.xlsm") ' Test
    Set wbFinance = Application.Workbooks("Finance.xlsm") ' Test
    Application.DisplayAlerts = False
    If Admin = True Then
        If MsgBox("This will purge ALL records showing Cancelled as well as," & vbNewLine & "all records showing Ready for Finance. Are you sure?", vbQuestion + vbYesNo) = vbYes Then
            pSheet = CStr(Format(pStartDate, "dd-MMM-yyyy")) & " - " & CStr(Format(pEndDate, "dd-MMM-yyyy"))
            cSheet = CStr(Format(cStartDate, "dd-MMM-yyyy")) & " - " & CStr(Format(cEndDate, "dd-MMM-yyyy"))
            nSheet = CStr(Format(nStartDate, "dd-MMM-yyyy")) & " - " & CStr(Format(nEndDate, "dd-MMM-yyyy"))
            CreateSheetIf ("Cancelled")
            CreateSheetIf (pSheet)
            CreateSheetIf (cSheet)
            CreateSheetIf (nSheet)
            pFTarget = wbFinance.Worksheets(pSheet).UsedRange.Rows.Count
            cFTarget = wbFinance.Worksheets(cSheet).UsedRange.Rows.Count
            nFTarget = wbFinance.Worksheets(nSheet).UsedRange.Rows.Count
            CTarget = wbCancelled.Worksheets("Cancelled").UsedRange.Rows.Count
            Set wspFinance = wbFinance.Worksheets(pSheet)
            Set wscFinance = wbFinance.Worksheets(cSheet)
            Set wsnFinance = wbFinance.Worksheets(nSheet)
            Set wsCancelled = wbCancelled.Worksheets("Cancelled")
            '---------------------------
            ' Purging Cancelled Records
            '---------------------------
            Me.sysMsgBox.Value = " Purging records marked Cancelled..."
            MRCForm.Caption = "Monitoring and Response Centre [ Proccessing... Cancelled records... Please wait... ]"
            Application.ScreenUpdating = False
            If CTarget = 1 Then
                If Application.WorksheetFunction.CountA(wsCancelled.UsedRange) = 0 Then CTarget = 0
            End If
            Source = wsMRC.UsedRange.Rows.Count
            Set xRg = wsMRC.Range("AG2:AG" & Source)
            On Error Resume Next
            For K = 1 To xRg.Count
                If CStr(xRg(K).Value) = "Y" Then
                    xRg(K).EntireRow.Copy Destination:=wsCancelled.Range("A" & CTarget + 1)
                    xRg(K).EntireRow.Delete
                    CTotal = CTotal + 1
                    MRCForm.Caption = "Monitoring and Response Centre [ Proccessing... (" & CTotal & ") Please wait... ]"
                    If CStr(xRg(K).Value) = "Y" Then
                        K = K - 1
                    End If
                    CTarget = CTarget + 1
                End If
            Next
            wsCancelled.Columns("A:AI").AutoFit
            Application.ScreenUpdating = True
            MRCForm.Caption = "Monitoring and Response Centre [ Proccessing... ]"
            Me.sysMsgBox.Value = " Purging records, marked Ready for Finance..."
            '-------------------------
            ' Perivous Finance Period
            '-------------------------
            MRCForm.Caption = "Monitoring and Response Centre [ Proccessing... " & pSheet & " Ready to Finance records... Please wait... ]"
            Me.sysMsgBox.Value = " Purging records, between " & pSheet & ", marked Ready for Finance..."
            Application.ScreenUpdating = False
            If pFTarget = 1 Then
                If Application.WorksheetFunction.CountA(wspFinance.UsedRange) = 0 Then pFTarget = 0
            End If
            Source = wsMRC.UsedRange.Rows.Count
            Set xRg = wsMRC.Range("AH2:AH" & Source)
            Set dRg = wsMRC.Range("B2:B" & Source)
            On Error Resume Next
            For K = 1 To xRg.Count
                If dRg(K).Value >= pStartDate And dRg(K).Value <= pEndDate Then
                    If CStr(xRg(K).Value) = "Y" Then
                        xRg(K).EntireRow.Copy Destination:=wspFinance.Range("A" & pFTarget + 1)
                        xRg(K).EntireRow.Delete
                        pFTotal = pFTotal + 1
                        MRCForm.Caption = "Monitoring and Response Centre [ Proccessing... " & pSheet & " (" & pFTotal & ") Please wait... ]"
                        If CStr(xRg(K).Value) = "Y" Then
                            K = K - 1
                        End If
                        pFTarget = pFTarget + 1
                    End If
                End If
            Next
            wspFinance.Columns("A:AI").AutoFit
            Application.ScreenUpdating = True
            '------------------------
            ' Current Finance Period
            '------------------------
            MRCForm.Caption = "Monitoring and Response Centre [ Proccessing... " & cSheet & " Ready to Finance records... Please wait... ]"
            Me.sysMsgBox.Value = " Purging records, between " & cSheet & ", marked Ready for Finance..."
            Application.ScreenUpdating = False
            If cFTarget = 1 Then
                If Application.WorksheetFunction.CountA(wscFinance.UsedRange) = 0 Then cFTarget = 0
            End If
            Source = wsMRC.UsedRange.Rows.Count
            Set xRg = wsMRC.Range("AH2:AH" & Source)
            Set dRg = wsMRC.Range("B2:B" & Source)
            On Error Resume Next
            For K = 1 To xRg.Count
                If dRg(K).Value >= cStartDate And dRg(K).Value <= cEndDate Then
                    If CStr(xRg(K).Value) = "Y" Then
                        xRg(K).EntireRow.Copy Destination:=wscFinance.Range("A" & cFTarget + 1)
                        xRg(K).EntireRow.Delete
                        cFTotal = cFTotal + 1
                        MRCForm.Caption = "Monitoring and Response Centre [ Proccessing... " & cSheet & " (" & cFTotal & ") Please wait... ]"
                        If CStr(xRg(K).Value) = "Y" Then
                            K = K - 1
                        End If
                        cFTarget = cFTarget + 1
                    End If
                End If
            Next
            wscFinance.Columns("A:AI").AutoFit
            Application.ScreenUpdating = True
            '---------------------
            ' Next Finance Period
            '---------------------
            MRCForm.Caption = "Monitoring and Response Centre [ Proccessing... " & nSheet & " Ready to Finance records... Please wait... ]"
            Me.sysMsgBox.Value = " Purging records, between " & nSheet & ", marked Ready for Finance..."
            Application.ScreenUpdating = False
            If nFTarget = 1 Then
                If Application.WorksheetFunction.CountA(wsnFinance.UsedRange) = 0 Then nFTarget = 0
            End If
            Source = wsMRC.UsedRange.Rows.Count
            Set xRg = wsMRC.Range("AH2:AH" & Source)
            Set dRg = wsMRC.Range("B2:B" & Source)
            On Error Resume Next
            For K = 1 To xRg.Count
                If dRg(K).Value >= nStartDate And dRg(K).Value <= nEndDate Then
                    If CStr(xRg(K).Value) = "Y" Then
                        xRg(K).EntireRow.Copy Destination:=wsnFinance.Range("A" & nFTarget + 1)
                        xRg(K).EntireRow.Delete
                        nFTotal = nFTotal + 1
                        MRCForm.Caption = "Monitoring and Response Centre [ Proccessing... " & nSheet & " (" & nFTotal & ") Please wait... ]"
                        If CStr(xRg(K).Value) = "Y" Then
                            K = K - 1
                        End If
                        nFTarget = nFTarget + 1
                    End If
                End If
            Next
            wsnFinance.Columns("A:AI").AutoFit
            Application.ScreenUpdating = True
            UpdateStatus
            wsMRC.Activate
            Application.CutCopyMode = False
            Total = CTotal + pFTotal + cFTotal + nFTotal
            FTotal = pFTotal + cFTotal + nFTotal
            If Total = 0 Then
                Me.sysMsgBox.Value = " Admin: No records found. No records have been purged. "
                MsgBox " Admin: No records found. No records have been purged. "
            Else
                Me.sysMsgBox.Value = " Admin: " & Total & " total record(s) purged. " & FTotal & " record(s) marked ready for finace and " & CTotal & " marked cancelled. "
                MsgBox " Admin: " & Total & " total record(s) purged. " & FTotal & " record(s) marked ready for finace and " & CTotal & " marked cancelled. "
            End If
        Else
            Me.sysMsgBox.Value = " Admin: Cancelled. No records have been purged. "
            MsgBox " Admin: Cancelled. No records have been purged. "
            UpdateStatus
        End If
    Else
        If MsgBox("This will purge ALL records showing Cancelled, Are you sure?", vbQuestion + vbYesNo) = vbYes Then
            CreateSheetIf ("Cancelled")
            Set wsCancelled = wbCancelled.Worksheets("Cancelled")
            CTarget = wbCancelled.Worksheets("Cancelled").UsedRange.Rows.Count
            '---------------------------
            ' Purging Cancelled Records
            '---------------------------
            Me.sysMsgBox.Value = " Purging records marked Cancelled..."
            MRCForm.Caption = "Monitoring and Response Centre [ Proccessing... Cancelled records... Please wait... ]"
            Application.ScreenUpdating = False
            If CTarget = 1 Then
                If Application.WorksheetFunction.CountA(wsCancelled.UsedRange) = 0 Then CTarget = 0
            End If
            Source = wsMRC.UsedRange.Rows.Count
            Set xRg = wsMRC.Range("AG2:AG" & Source)
            On Error Resume Next
            For K = 1 To xRg.Count
                If CStr(xRg(K).Value) = "Y" Then
                    xRg(K).EntireRow.Copy Destination:=wsCancelled.Range("A" & CTarget + 1)
                    xRg(K).EntireRow.Delete
                    CTotal = CTotal + 1
                    MRCForm.Caption = "Monitoring and Response Centre [ Proccessing... (" & CTotal & ") Please wait... ]"
                    If CStr(xRg(K).Value) = "Y" Then
                        K = K - 1
                    End If
                    CTarget = CTarget + 1
                End If
            Next
            wsCancelled.Columns("A:AI").AutoFit
            Application.ScreenUpdating = True
            If CTotal = 0 Then
                Me.sysMsgBox.Value = " User: No records found, marked cancelled. No records have been purged. "
                MsgBox " User: No records found, marked cancelled. No records have been purged. "
            Else
                Me.sysMsgBox.Value = " User: " & CTotal & " record(s) have been purged. "
                MsgBox " User: " & CTotal & " record(s) have been purged. "
            End If
        Else
            Me.sysMsgBox.Value = " User: Cancelled. No records have been purged. "
            MsgBox " User: Cancelled. No records have been purged. "
        End If
    End If
    Application.Workbooks("Cancelled.xlsm").Close SaveChanges:=True, CreateBackup:=True ' Test
    Application.Workbooks("Finance.xlsm").Close SaveChanges:=True, CreateBackup:=True ' Test
    nLastRowMRC = wsMRC.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    nCurrentRowMRC = nLastRowMRC
    nNextRowMRC = nLastRowMRC + 1
    UpdateStatus
    UpdateScreen
    wsMRC.Columns("A:AI").AutoFit
    wsMRC.Activate
    Application.CutCopyMode = False
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True


End Sub

The create sheet if function is as follows:

Code: 
Function CreateSheetIf(strSheetName As String) As Boolean

    Dim wsTest As Worksheet
    CreateSheetIf = False
    Set wsTest = Nothing
    On Error Resume Next
    If strSheetName = "Cancelled" Then
        Set wsTest = wbCancelled.Worksheets(strSheetName)
    Else
        Set wsTest = wbFinance.Worksheets(strSheetName)
    End If
    On Error GoTo 0
    If wsTest Is Nothing Then
        CreateSheetIf = True
        ThisWorkbook.Sheets("Template").Visible = True
        If strSheetName = "Cancelled" Then
            ThisWorkbook.Sheets("Template").Copy after:=wbCancelled.Sheets(Sheets.Count)
            ActiveSheet.Name = strSheetName
        Else
            ThisWorkbook.Sheets("Template").Copy after:=wbFinance.Sheets(Sheets.Count)
            ActiveSheet.Name = strSheetName
        End If
        ThisWorkbook.Sheets("Template").Visible = False
    End If

Like I said, the code is not very clean or fast, but it works. Hope this helps someone else. I'm sure there are better ways to accomplish what I need, but I have yet to get any help from this site and had to come up with something.
 
Upvote 0
Re: Need help moving rows to another sheet or workbook

Originally you said:

I have been stuck on this for awhile now, need to move rows to another worksheet or workbook. The 1st part should find all rows marked cancelled (Column AG is "Y"). 2nd part should find all rows marked ready (Column AH is "Y"). Code runs, but results are very strange. Does not move all rows, also seams to copy row 1 (headers). This is what I have so far:

Questions:
1. Copy row to what sheet? Give me the sheet name.
2. So if in column AG or AH you have the value "Y" you want this row copied to sheet named ??
Is that correct?

And you mean "Y" not "y" correct.

It sure looks like your script is attempting to do a lot more then this.


​
 
Last edited:
Upvote 0
Re: Need help moving rows to another sheet or workbook

My Aswer Is This said:
Originally you said:

I have been stuck on this for awhile now, need to move rows to another worksheet or workbook. The 1st part should find all rows marked cancelled (Column AG is "Y"). 2nd part should find all rows marked ready (Column AH is "Y"). Code runs, but results are very strange. Does not move all rows, also seams to copy row 1 (headers). This is what I have so far:

Questions:
1. Copy row to what sheet? Give me the sheet name.
2. So if in column AG or AH you have the value "Y" you want this row copied to sheet named ??
Is that correct?

And you mean "Y" not "y" correct.

It sure looks like your script is attempting to do a lot more then this.


​
Click to expand...


Thanks for the replay, I have already solved the issues. The above code is the completed version I have come up with. Yes, Columns AG and AH is "Y" not "y". As for the sheet name, there is one "Cancelled" and the others the name changes based on the finance period. Again, I have solved the issue and posted what I have come up with above. It works just fine, not very clean coding, but it works.

The code, in the CreateSheetIf function (do not remember where I found this code online, but had to modify it a little for my needs), checks the workbooks Cancelled and Finance for specific worksheets. If they do not exist, it copies a template from the main workbook into the Cancelled workbook then copies the template from main workbook into the Finance workbook, renaming the worksheet based on current finance period. Then it goes through the main database, row by row, looking at Columns AG and AH for all records marked Y. If it finds a record in column AG marked Y, it will move it to the Cancelled workbook into the last row available in the Cancelled worksheet. If it finds a record in column AH marked Y, within a certain date period in column B, it will move that row into the Finance workbook, into the last row available within that date periods worksheet.

Hope this can help someone else solve their own similar issues.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Copy/Move Data from a table to another Sheet to a table
Replies
2
Views
298
himaruasuka
H
B
  • Question Question
update table
Replies
0
Views
436
BobtBuilder
B
J
  • Question Question
Copy row from Workbook to another when Checkbox is checked
Replies
0
Views
711
JohnTester
J
P
  • Question Question
ListBox Click to textbox & delete row
Replies
0
Views
592
pure vito
P
E
  • Question Question
Issues When Using VBA to Automatically Move Row to Another Sheet Based On Cell Value
2 3
Replies
21
Views
3K
EBe
E

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

Share this page

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