VBA to get date from 1 sheet then use that date to find same date on other sheet and offset/paste data

[SportsBettor]

I have a workbook that needs daily updating of 72 different sheets. I want to use todays date located on "SheetList" (I1) as a search parameter to determine where data gets pasted. The data is located in the same range on each sheet (G2:G365) I then have a column on every sheet that lists the date range I am using (L2:L145). When the date is matched in (L2:145) I need to transpose all the cells copied in range (G2:G365) starting in column M next to the correct date. The data is copied and pasted on the same sheet but I need to do it over all 72 sheets. I already have the macro to copy the data and put it in the (G2:G365) range but I am stuck on figuring out how to locate the date in column L then offset to column M and transpose the cells. Not all copied cells have data but that should not be an issue as I want to copy and paste the whole range.
So in a nutshell the date listed in cell I1 on "SheetList"
copy data located on every sheet (G2:G365)
match date from cell "SheetList" (I1) to correct row of (L2:145)
Paste values (Numbers not text) into columns (M:NL) beside correct date
I would prefer to use sheet codes vs sheet names as well.
I have found many different scenarios but they all copy data to a different sheets, not the same one, and nothing seems look do an index match type function to determine where to put the data. I am new to VBA and have read a ton of conflicting ways to get info but nothing includes searching for where to place it.


This is the code I am using to copy the values I need posted offset to column "M" from the Date listed in column "L2:L145"
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Sub AAA_AllDailyUpdates()
'
' AAA_AllDailyUpdates Macro
'
Sheets("VsRPI1-25-1st").Select
Range("F2:F365").Select
Application.CutCopyMode = False
Selection.Copy
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("VsRPI1-25-2nd").Select
Range("F2:F365").Select
Application.CutCopyMode = False
Selection.Copy
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("VsRPI1-25HomeFinal").Select
Range("F2:F365").Select
Application.CutCopyMode = False
Selection.Copy
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False</code>End Sub

Since that macro leaves all the cells highlighted after it runs on all 72 sheets I would like to then paste those ranges onto their respective sheets by looking up the current date listed in sheet "SheetList" cell "I1" finding the matching date in "L2:L145" and offset to Column "M"


<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;"> Sub Transpose()
Sheets("VsRPI1-25Final").Select
Range("G2:G365").Select
Selection.Copy
Range("M94").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

Sheets("VsRPI1-25-1st").Select
Range("G2:G365").Select
Application.CutCopyMode = False
Selection.Copy
Range("M94").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True</code>End Sub

I have been looking to find some code that is close to what I need but nothing I have tried has worked so far.

 

[DanteAmor]

Try with this:

Sub Copy_Transpose()
    'Copy and Transpose for all sheets
    
    Dim s As Worksheet, wDate As Date, cell As Range
    Dim wRow As Double
    
    Application.ScreenUpdating = False
    
    sdate = Sheets("SheetList").Range("I1")
    
    For Each s In Sheets
        s.Range("F2:F365").Copy
        s.Range("G2").PasteSpecial xlPasteValues
        wRow = 0
        For Each cell In s.Range("L2:L145")
            If cell.Value = sdate Then
                wRow = cell.Row
                Exit For
            End If
        Next
        If wRow > 0 Then
            s.Range("G2:G365").Copy
            s.Range("M" & wRow).PasteSpecial Paste:=xlPasteAll, Transpose:=True
        End If
    Next
    
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
    
    MsgBox "End"


End Sub