Posts by scttsmn

Re: Identifying a variety of holidays in a data set

I found a solution that will work for me - I did not use the code from my original post. Below is an excerpt of the coding from my Macro (granted, all the variables are not defined in this excerpt). I embedded an IsDate function in an IF statement, to verify that the cell value was a date - if the value is not a date, then the "LSearchRowMod = LSearchRowMod + 1" portion moves the search to the next line. If a date is found and it matches the "month" and "day" criteria, then it is copied into Sheet2. I can then replicate this code for each date that I want to search.

Thank you again for your help.

srchDate = CDate("12/24/2000")


For Each rngCell In Range(SrchClmn & LSearchRowMod & ":" & SrchClmn & lngLstRow)
           
            If IsDate(Range(SrchClmn & CStr(LSearchRowMod))) Then
            
                If Month(Range(SrchClmn & CStr(LSearchRowMod)).Value) = Month(srchDate) And Day(Range(SrchClmn & CStr(LSearchRowMod)).Value) = Day(srchDate) Then
                    
                    'Select row in Sheet1 to copy
                    Rows(CStr(LSearchRowMod) & ":" & CStr(LSearchRowMod)).Select
                    Selection.Copy
                    
                    'Paste row into Sheet2 in next row
                    Sheets("Sheet2").Select
                    Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
                    ActiveSheet.Paste
                    
                    'Move counter to next row
                    LCopyToRow = LCopyToRow + 1
                    
                    'Go back to Sheet1 to continue searching
                    Sheets(shtName).Select
                    
                End If
                
                LSearchRowMod = LSearchRowMod + 1
                
            Else
            LSearchRowMod = LSearchRowMod + 1
            End If
            
        Next

Re: Identifying a variety of holidays in a data set

Thank you for your quick response!

This is certainly one way of accomplishing the task, however, I would rather not hard code absolute dates into the macro if I don't have to. I would like to set up the macro to identify "December 25" in ANY year (not just 2015). Could this existing code be modified to achieve this task? The more advanced application would be that I would use the macro to identify a variety of dates (in any year).

Thank you again!

While searching the internet, I came across the following code, which searches a data set for the macro-defined search terms, and copies them into a separate workbook. This code works splendidly, however, I would like to modify it to search for certain holidays. However, I have never worked with these types of date-defining/constricting codes before. In a simple example, how might I modify this code to search for January 1, December 24, and December 25... instead of Fruit 2, Fruit 5, and Fruit 18?

Thank you in advance for your help!

Sub FruitBasket()
 
Dim rngCell As Range
Dim lngLstRow As Long
Dim strFruit() As String
Dim intFruitMax As Integer
 
intFruitMax = 3
ReDim strFruit(1 To intFruitMax)
 
strFruit(1) = "Fruit 2"
strFruit(2) = "Fruit 5"
strFruit(3) = "Fruit 18"
 
lngLstRow = ActiveSheet.UsedRange.Rows.Count
 
For Each rngCell In Range("A2:A" & lngLstRow)
    For i = 1 To intFruitMax
        If strFruit(i) = rngCell.Value Then
            rngCell.EntireRow.Copy
                Sheets("Inventory").Select
                    Range("A65536").End(xlUp).Offset(1, 0).Select
                    Selection.PasteSpecial xlPasteValues
                Sheets("Fruit").Select
        End If
    Next i
Next
 
End Sub

Re: Macro to replicate the naming convention of adding a new worksheet

Thanks Bruce! This worked like a charm! I added a couple lines to force the new sheet to the right of the original sheet!

Sub NewSheet()
shtName = ActiveSheet.Name
            Dim ws As Worksheet
            Dim newNum As Long, c As Long
            For Each ws In Worksheets
                If ws.Name Like "Results*" And Len(ws.Name) > 7 Then
                    c = CLng(Right(ws.Name, Len(ws.Name) - 7))
                        newNum = WorksheetFunction.Max(c + 1, newNum)
                End If
                Next ws
                Worksheets.Add(After:=Sheets(shtName)).Name = "Results" & newNum
End Sub

Thanks again!

I am trying to write a macro in which the macro will insert a new worksheet similar to how a new sheet would normally be added. The difference, however, is that I would like the name of the new sheet to be "Results" and follow the naming convention that is standard for new sheets (Results1, Results2, Results3.... so on an so forth) so that each time the macro is run, a new sheet will be generated.

I am familiar with the coding to insert a new sheet, but how do I get the macro to search the existing sheets' names to find the "Results#" tab with the largest number, and then follow the naming convention for the next new tab?

Thank you for your help.