Results 1 to 4 of 4

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

  1. #1
    Neophyte ljhansen's Avatar
    Join Date
    Jul 2020
    Posts
    2
    Articles
    0
    Excel Version
    2016

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!
    Attached Files Attached Files

  2. #2
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    293
    Articles
    0
    Excel Version
    2007
    .
    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
    Attached Files Attached Files

  3. #3
    Neophyte ljhansen's Avatar
    Join Date
    Jul 2020
    Posts
    2
    Articles
    0
    Excel Version
    2016
    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!
    Last edited by ljhansen; 2020-07-24 at 08:54 PM.

  4. #4
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    293
    Articles
    0
    Excel Version
    2007
    You are welcome.

Tags for this Thread

Posting Permissions

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