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

stirlingmw1

Board Regular
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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Fluff

MrExcel MVP, Moderator
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``````

stirlingmw1

Board Regular
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

Fluff

MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
0
Views
225
Replies
14
Views
225
Replies
1
Views
171
Replies
8
Views
315
Replies
9
Views
477

1,148,236
Messages
5,745,545
Members
423,959
Latest member
Bismarkmensah

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.

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

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