# 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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### 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,249
Messages
5,745,627
Members
423,964
Latest member
Dustin M

### 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.

### Which adblocker are you using?

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