Loop through row and copy data from worksheet of the cell value matches sheet name

[stirlingmw1]

I have a workbook that contains multiple worksheets (more can be added). Each worksheet will have a ships name (shortened to 4 letters) plus a financial year as its Name, i.e. Blyh_22-23. These sheets hold qty's of ammunition with a total for each row in range O3:O28. I also have a main worksheet for each financial year "Main_22-23" that collates all of the totals for each financial year.

Row 1 Column C onwards has a ships name "C1 - Blyh, D1 - Bngr, E1 - Broc" with a final Sum in the final column. I am using the following code to look through the "Main" sheet, row 1 and if the cell value plus "_22-23" (this year will eventually come from a combobox, but im using 22-23 to test my code) is equal to a worksheet name then data from column O of that worksheets is copied and pasted in the right column below the ships name.

The problem I am having is that the data is added to "Main" in random columns and not actually under the ships name the data refers to.

Dim wkSht As Worksheet
Dim i As Long
Dim LastCol As Long
Dim LastRow As Long

LastRow = Sheets("Minor_22-23").Cells(Sheets("Minor_22-23").Rows.Count, "A").End(xlUp).Row
LastCol = Sheets("Minor_22-23").Cells(1, Sheets("Minor_22-23").Columns.Count).End(xlToLeft).Column - 1

i = 3
    For Each wkSht In Sheets

        For Each Cell In Sheets("Minor_22-23").Range("C1:Y1")

            If Cell + "_22-23" = wkSht.Name Then

                On Error Resume Next

                Sheets("Minor_22-23").Range(Cells(3, i), Cells(LastRow, i)).Value = wkSht.Range("O3:O28").Value
                i = i + 1
            End If
        Next Cell
    Next wkSht

Any idea what I am doing wrong.

regards

Steve

 

[Fluff]

How about

Dim wkSht As Worksheet
Dim LastCol As Long
Dim LastRow As Long

LastRow = Sheets("Minor_22-23").Cells(Sheets("Minor_22-23").Rows.Count, "A").End(xlUp).Row
LastCol = Sheets("Minor_22-23").Cells(1, Sheets("Minor_22-23").Columns.Count).End(xlToLeft).Column - 1

    For Each wkSht In Worksheets

        For Each cell In Sheets("Minor_22-23").Range("C1:Y1")

            If cell + "_22-23" = wkSht.Name Then
                With Sheets("Main_22-23")
                  .Range(.Cells(3, cell.Column), .Cells(LastRow, cell.Column)).Value = wkSht.Range("O3:O28").Value
                End With
            End If
        Next cell
    Next wkSht

 

[stirlingmw1]

How about

Dim wkSht As Worksheet
Dim LastCol As Long
Dim LastRow As Long

LastRow = Sheets("Minor_22-23").Cells(Sheets("Minor_22-23").Rows.Count, "A").End(xlUp).Row
LastCol = Sheets("Minor_22-23").Cells(1, Sheets("Minor_22-23").Columns.Count).End(xlToLeft).Column - 1

    For Each wkSht In Worksheets

        For Each cell In Sheets("Minor_22-23").Range("C1:Y1")

            If cell + "_22-23" = wkSht.Name Then
                With Sheets("Main_22-23")
                  .Range(.Cells(3, cell.Column), .Cells(LastRow, cell.Column)).Value = wkSht.Range("O3:O28").Value
                End With
            End If
        Next cell
    Next wkSht

Thanks mate, I almost had it.

Works a treat.

Regards

Steve

 

[Fluff]

You're welcome & thanks for the feedback.