Creating 3 new sheets & then pull data from a file name to create a date

Hi

I have just been given a problem that I know I need a macro to perform, but it is way above anything I have done before, so I am hoping that someone here can help.

Attached is a sample file and also the ideal results file I would like to achieve through the Macro.

In a nutshell this is what I want to do:

1. Create a sheet that only has "approved" records in it shown in col U
2. Create a sheet that only has "non approved" records in it also shown in col U (basically everything that is not approved)
3. Create a Summary sheet for the "approved" records.

With regards to the Summary Sheet:-

Col A needs to total the values in Col A on the "Approved" Sheet
Col B will always be GBP
Col C needs to populate the date from either the file name, or from cell C1 on the "approved" sheet.
Col D needs to find the day of the week based on the date shown in Col C.
Col E needs to check the day shown in col D : If it is Sunday / Monday / Tuesday or Wednesday then the result should be Col C date + 3 days. If Thursday or Sat + 4 days, If Friday + 5 days.

Really struggling to even see where to begin.

Any and all help massively appreciated.

Kind regards

Jon

Give this a try:

Option Explicit

Sub Reformat()
Dim wsApp As Worksheet
Dim wsNon As Worksheet
Dim wsSum As Worksheet
Dim LR    As Long

Sheets(1).Copy Before:=Sheets(1)
Sheets(1).Name = "Problem Files"
Worksheets.Add(Before:=Sheets(1)).Name = "Approved"
Worksheets.Add(Before:=Sheets(1)).Name = "Summary"
Set wsNon = Sheets("Problem Files")
Set wsApp = Sheets("Approved")
Set wsSum = Sheets("Summary")

With wsNon
    .Rows(2).AutoFilter
    .Rows(2).AutoFilter 21, "Approved"
    LR = .Range("A" & .Rows.Count).End(xlUp).Row
    If LR > 2 Then
        .Range("A2:A" & LR).EntireRow.Copy wsApp.Range("A1")
        .Range("A3:A" & LR).EntireRow.Delete xlShiftUp
    End If
    .AutoFilterMode = False
End With

With wsSum
    With .Range("A1:E1")
        .Value = [{"Amount","CurrencyCode","Processed Date","Processed Day","Release Date"}]
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
        .Font.Size = 12
        .ColumnWidth = 14
    End With
    
    .Range("A2").FormulaR1C1 = "=SUM(Approved!C)"
    .Range("B2") = "GPD"
    .Range("C2").FormulaR1C1 = "=RIGHT('Problem Files'!R[-1]C,10)+0"
    .Range("C2,E2").NumberFormat = "mm/dd/yy;@"
    .Range("D2").FormulaR1C1 = "=RC[-1]"
    .Range("D2").NumberFormat = "dddd"
    .Range("E2").FormulaR1C1 = "=RC[-2]+ LOOKUP(WEEKDAY(RC[-2]),{1,5,6,7},{3,4,5,4})"
    .UsedRange.Value = .UsedRange.Value
    .Columns.AutoFit
    .UsedRange.HorizontalAlignment = xlCenter
End With

End Sub

That's weird, the only way that row at the bottom would get copied to the APPROVED sheet is if that row had the word "approved" in column U. Take that out if it does. The Summary sheet is only adding the column A from the APPROVED sheet, so I don't see how any rows that shouldn't be there would get onto that sheet.

Glad to help.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

There you go...

Jon, this thread/question has been resolved. Please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

Then post your new question in a new thread, you will garner the most attention from new eyes, and it's the rules.