VBA - add sheet name to last column for an array of sheets

[Js Smith]

Happy Pi day to you all.
Once again chasing my tail. Attempting to set an array of sheets in a workbook, find the last column (could be Column B, Z or anything in between) then from the 2nd row to last row enter the sheet's name as a value.
The last row could be likewise variable.

Sub AddSourceCol()

Dim sh As Variant, i As Long, j As Long, LastCol As Long, LastRow As Long, ws As Worksheet

For Each sh In Array("Epics V#1", "Epics V#2", "Epics V#3")

    Set ws = ActiveSheet
'    Set ws = sh
    LastCol = ActiveSheet.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    LastRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
      Sheets(sh).Cells(1, LastCol + 1).Value = "Source"
      Sheets(sh).Cells(2, LastCol + 1) = ws.Name <----Enters Epics V#3 for the cell in the 2nd row of the Source column in every sheet in the array

'      Sheets(sh).Range(Sheets(sh).Cells(2, LastCol + 1)).AutoFill Destination:=Sheets(sh).Range(Sheets(sh).Cells(2, LastCol + 1)): Sheets(sh).Cells (LastCol + 1 & LastRow)   <--- Runtime Error 1004
      
'     Sheets(sh).Range(Sheets(sh).Cells(2, LastCol + 1), Sheets(sh).Cells(LastRow, LastCol + 1)) = ws.Name    <----Enters Epics V#3 for the Source column in every sheet in the array
     
      
Next

MsgBox "Done!"

End Sub

I can't install MiniSheet here due to Group Policy restrictions but here's an Idea of what the data might look like:

Epics V#1\Col A	Epics V#1\Col B	Source (what vba does)	Source (desired result)
123​	12/1/2022​	Epics V#3	Epics V#1
124​	12/2/2022​	Epics V#3	Epics V#1
125​	12/3/2022​	Epics V#3	Epics V#1
126​	12/4/2022​	Epics V#3	Epics V#1
127​	12/5/2022​	Epics V#3	Epics V#1

Epics V#2\Col A	Epics V#2\Col B	Epics V#2\Col c	Epics V#2\Col d	Source (what vba does)	Source (desired result)
547​	12/1/2022​	5000​	1/1/2023​	Epics V#3	Epics V#2
548​	12/2/2022​	5001​	1/2/2023​	Epics V#3	Epics V#2

Epics V#3\Col A	Epics V#3\Col B	Epics V#3\Col c	Epics V#3\Col d	Epics V#3\Col E	Epics V#3\Col F	Source (what vba does)	Source (desired result)
687​	12/1/2022​	5000​	1/1/2023​	9998​	12/1/2022​	Epics V#3	Epics V#3
688​	12/2/2022​	5001​	1/2/2023​	9999​	12/2/2022​	Epics V#3	Epics V#3
689​	12/3/2022​	5002​	1/3/2023​	10000​	12/3/2022​	Epics V#3	Epics V#3
690​	12/4/2022​	5003​	1/4/2023​	10001​	12/4/2022​	Epics V#3	Epics V#3
691​	12/5/2022​	5004​	1/5/2023​	10002​	12/5/2022​	Epics V#3	Epics V#3

How would you perform this task? Thank you for your insights!

 

[rlv01]

Happy Pi day to you all.
Once again chasing my tail. Attempting to set an array of sheets in a workbook, find the last column (could be Column B, Z or anything in between) then from the 2nd row to last row enter the sheet's name as a value.
The last row could be likewise variable

If I understand you correctly, you want to:
1. Loop through the specified sheets.
2. Find the last column
3. Overwrite the values in the last column (excluding the header row) with the worksheet's name.

Sub AddSourceCol()
    Dim LastCol As Long, WS As Worksheet
    Dim rng As Range
    
    Set WS = ActiveSheet
    
    For Each WS In Worksheets
        Select Case WS.Name
            Case "Epics V#1", "Epics V#2", "Epics V#3"
                LastCol = WS.UsedRange.Columns.Count
                Set rng = Application.Intersect(WS.UsedRange, WS.UsedRange.Columns(LastCol).EntireColumn)
                With rng
                    .Offset(1).Resize(.Rows.Count - 1).Value = WS.Name                 'overwrites last column
                End With
        End Select
    Next WS
End Sub

 

[Js Smith]

If I understand you correctly, you want to:
1. Loop through the specified sheets.
2. Find the last column
3. Overwrite the values in the last column (excluding the header row) with the worksheet's name.

Sub AddSourceCol()
    Dim LastCol As Long, WS As Worksheet
    Dim rng As Range
   
    Set WS = ActiveSheet
   
    For Each WS In Worksheets
        Select Case WS.Name
            Case "Epics V#1", "Epics V#2", "Epics V#3"
                LastCol = WS.UsedRange.Columns.Count
                Set rng = Application.Intersect(WS.UsedRange, WS.UsedRange.Columns(LastCol).EntireColumn)
                With rng
                    .Offset(1).Resize(.Rows.Count - 1).Value = WS.Name                 'overwrites last column
                End With
        End Select
    Next WS
End Sub

It wasn't exactly what I needed but it was a simply matter to adjust it to add a column rather than replace an existing column, once you straightened out my loop efforts.
Thank you for the help and the education!

I will be picking this apart to further my VBA education.