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

stirlingmw1

Board Regular
Joined
Jun 17, 2016
Messages
53
Office Version
  1. 2016
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
How about
VBA Code:
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
 
Upvote 0
Solution
How about
VBA Code:
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
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top