Macro not properly looping through the workbook

Hello everyone,

I'm having a hard time figuring how to correct my faulty code, and hours of Googling still doesn't give me a hint.

Here is my code

Private Sub Update()

Application.ScreenUpdating = True

Dim ws As Worksheet Dim LRow As Integer Dim PathName, Filename, Col As Variant PathName = Worksheets("Param").Range("B3").Value Filename = Worksheets("Param").Range("B4").Value

Workbooks.Open Filename:=PathName & Filename ThisWorkbook.Activate

For Each ws In ThisWorkbook.Worksheets If ws.Name <> "Param" Then LRow = ws.Cells(Rows.Count, 2).End(xlUp).Row Col = WorksheetFunction.Match("Week " & Right(Worksheets("Param").Range("D2"), 2) - 1, ws.Rows(1), 0) + 1 If Not IsError(Col) Then ws.Columns(Col).Insert Worksheets("Param").Range("D2").Copy Destination:=ws.Cells(1, Col) Worksheets("Param").Range("A6").Copy Destination:=ws.Range(Cells(2, Col), Cells(LRow - 1, Col)) ws.Range(Cells(2, Col), Cells(LRow - 1, Col)).Copy ws.Range(Cells(2, Col), Cells(LRow - 1, Col)).PasteSpecial xlPasteValues

'Apply same format as previous column & sum the column ws.Range(Cells(1, Col - 1), Cells(LRow, Col - 1)).Copy ws.Range(Cells(1, Col), Cells(LRow, Col)).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False ws.Cells(LRow, Col - 1).Copy Destination:=ws.Cells(LRow, Col) End If Next ws

Application.ScreenUpdating = True

End Sub

Param!$D2 = "Week 21", and Param!$B3 and B4 are respectively the pathname and filename of another workbook which I'm opening to pull out data from.

What the macro is basically supposed to do is to go into each other worksheets, excluding the first one called "Param", find the cell containing "Week 20" (which is in the first row) and add a column after it.

My issues are the following:

* If I'm running the macro from the "Param" worksheet, I'm getting a Run-time Error 13 mismatch on

> Col = Application.Match("Week " & Right(Worksheets("Param").Range("D2"), 2) - 1, Rows(1), 0) + 1

* When I'm running the macro from another worksheet, the macro works but instead of adding a column in each other worksheets, it adds all the columns in the same, original worksheets (so it'll add 6 columns in Sheet1 if I have 6 worksheets (7, including Param)).

I suppose my issue lies in the 'Col' variant, but what would be the right way to fix this?