Create new sheet monthly and bring over rows meeting certain criteria in column J

ljhansen · Jul 21, 2020

Hi,
On the first of each new month, I would like my workbook to automatically generate a new sheet for the new month and copy over rows that meet certain criteria in column J, that way I know the loan still needs attention from the previous month. I have been working with a code to generate the new sheet when the new month starts: it currently generates a new sheet from the Master sheet--which is fine for the first time as I don't have any June data to carry over, but I want it to generate from the previous month's sheet so that it copies over Unclosed loans from the previous month, and it currently brings over all of the rows (once you open the workbook it'll do it), no matter their Status. Here is the current code I have that I'm hoping to modify, but I can't seem to find a good starting place to begin:

Code:

Sub Next_Month()
Dim ws As Worksheet
Dim wsM As Worksheet
Dim strName As String
Dim bCheck As Boolean
On Error Resume Next
Set wsM = Sheets("Loans Template")
strName = Format(Date, "mm_yyyy")
bCheck = Len(Sheets(strName).Name) > 0
If bCheck = False Then
'add new sheet after Instructions
    wsM.Copy After:=Sheets(1)
    ActiveSheet.Name = strName
End If
Set wsM = Nothing
End Sub

I want it to only copy and paste those rows into the new sheet whose value in column J is not "Closed;" I have quite a few other options in column J that can be chosen from:
Received Application, Corresponding with Member, Awaiting Documentation, In Decision-Making, Submitted for Docs, Docs Sent to Member, Awaiting Closing, Awaiting Usage

The range where data can be input on the sheet is A4:K52, but the later rows may not end up having any data in them, so I would want it to loop and find the last row meeting the criteria

Is this possible? Any help is much appreciated!

Logit · Jul 21, 2020

.
This is one method :

Code:

Sub Next_Month()
Dim ws As Worksheet
Dim wsM As Worksheet
Dim strName As String
Dim bCheck As Boolean


On Error Resume Next
Set wsM = Sheets("Loans Template")
strName = Format(Date, "mm_yyyy")
bCheck = Len(Sheets(strName).Name) > 0


If bCheck = False Then
'add new sheet after Instructions
    wsM.Copy After:=Sheets(1)
    ActiveSheet.Name = strName
End If


Set wsM = Nothing


CopyYes


End Sub


Sub CopyYes()
    Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet
    
    Dim myString As String


    ' Change worksheet designations as needed
    Set Source = ActiveWorkbook.Worksheets("Loans Template")
    Set Target = ActiveSheet
    
    'myString = "Closed"
    
    j = 2     ' Start copying to row 1 in target sheet
    For Each c In Source.Range("J4:J1000")   ' Do 1000 rows
        If c <> "Closed" Then
           Source.Rows(c.Row).Copy Target.Rows(j)
           j = j + 1
        End If
    Next c
End Sub

ljhansen · Jul 24, 2020

Thanks for the method! I modified so it would keep my formulas working and keep my header information as well. I'm afraid it will always use the Loans Template worksheet instead of the previous sheet when generating the new sheet on the first of each month. I'll play with this and see if I can make the correct modifications, thank you for the starting point!

Logit · Jul 24, 2020

You are welcome.

Create new sheet monthly and bring over rows meeting certain criteria in column J

ljhansen

New member

Attachments

Logit

Member

Attachments

ljhansen

New member

Logit

Member