Sub GenerateReport()
    Dim selectedMonth As Date
    Dim monthRange As Range
    Dim monthCell As Range
    Dim reportSheet As Worksheet
    Dim mainSheet As Worksheet
    Dim reportHeaders As Range
    Dim copyRange As Range
    Dim colIndex As Integer
    Dim newSheetName As String
    
    ' Set the main sheet (adjust "Sheet1" if your sheet has a different name)
    Set mainSheet = ThisWorkbook.Sheets("Sheet1")
    
    ' Retrieve the selected month from C1
    If IsDate(mainSheet.Range("C1").Value) Then
        selectedMonth = DateSerial(Year(mainSheet.Range("C1").Value), Month(mainSheet.Range("C1").Value), 1)
    Else
        MsgBox "Please select a valid month from the drop-down list in C1.", vbExclamation
        Exit Sub
    End If
    
    ' Define the range containing months J3:AS3
    Set monthRange = mainSheet.Range("J3:AS3")
    
    ' Initialize variable to store column index
    colIndex = 0
    
    ' Loop through each cell in the monthRange to find a match
    Dim cell As Range
    For Each cell In monthRange
        ' Compare only the month and year
        If Format(cell.Value, "mmmm-yy") = Format(selectedMonth, "mmmm-yy") Then
            colIndex = cell.Column
            Exit For
        End If
    Next cell
    
    ' Check if the month was found
    If colIndex = 0 Then
        MsgBox "Selected month not found in the range J3:AS3.", vbCritical
        Exit Sub
    End If
    
    ' Create a valid sheet name
    newSheetName = Format(selectedMonth, "mmmm-yy")
    
    ' Check if the sheet already exists
    On Error Resume Next
    Set reportSheet = ThisWorkbook.Sheets(newSheetName)
    On Error GoTo 0
    
    If Not reportSheet Is Nothing Then
        MsgBox "A report for " & newSheetName & " already exists.", vbInformation
        Exit Sub
    End If
    
    ' Add a new sheet and name it
    Set reportSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    On Error Resume Next
    reportSheet.Name = newSheetName
    If Err.Number <> 0 Then
        MsgBox "Unable to name the sheet. Please ensure the name is unique and valid.", vbCritical
        Application.DisplayAlerts = False
        reportSheet.Delete
        Application.DisplayAlerts = True
        Exit Sub
    End If
    On Error GoTo 0
    
    ' Copy headers from A3:I3 to A1:I1 in reportSheet
    mainSheet.Range("A3:I3").Copy Destination:=reportSheet.Range("A1")
    
    ' Copy the selected month data from the identified column (e.g., J) to J1 in reportSheet
    mainSheet.Cells(3, colIndex).Copy Destination:=reportSheet.Range("J1")
    
    ' Determine the last row with data in the main sheet (assuming column A has data)
    Dim lastRow As Long
    lastRow = mainSheet.Cells(mainSheet.Rows.Count, "A").End(xlUp).Row
    
    ' Copy data from A4:I(lastRow) to A2:I(lastRow-2) in reportSheet
    If lastRow >= 4 Then
        mainSheet.Range("A4:I" & lastRow).Copy Destination:=reportSheet.Range("A2")
        ' Copy the selected month’s data (e.g., J4:JlastRow) to J2 in reportSheet
        mainSheet.Range(mainSheet.Cells(4, colIndex), mainSheet.Cells(lastRow, colIndex)).Copy Destination:=reportSheet.Range("J2")
    End If
    
    ' Optional: Format the report
    With reportSheet
        .Columns.AutoFit
        .Range("A1:I1").Font.Bold = True
        .Range("J1").Font.Bold = True
        ' Apply borders
        .Range("A1:J" & (lastRow - 2)).Borders.LineStyle = xlContinuous
    End With
    
    MsgBox "Report for " & newSheetName & " has been generated successfully.", vbInformation
End Sub
i have this vba code. in this code while generating reports it copies formulas in new sheet but i want to copy values in new generated report.