-1

I have 5 sheets and each sheet represents a team starting from team 1 to team 5. Now I want refer one specific cell for each sheet and summarize them as a row in my active sheet (for example: a-e) 

     Team 1 Team#2  Team#3  Team#4  Team#5   
         a        b      c       d       e

My code works but it's too tedious. I wonder how I can use a loop to replace the following code? I guess I need a subloop, one loop is for different sheets, the other loop is for different columns for activesheets. 

Sub Macro1()
Range("B3").Value = Sheet2.Range("C2")
End Sub
SecretAgentMan
  • 2,856
  • 7
  • 21
  • 41
John
  • 1
  • 1
  • 1
    Maybe start with a search for "Excel VBA loop worksheets"? [Here's](https://stackoverflow.com/questions/25953916/excel-vba-looping-through-multiple-worksheets) one example. – BigBen Jan 16 '20 at 17:06
  • Why are you using VBA instead of formulas in the cells? – SmileyFtW Jan 16 '20 at 17:24
  • I have like 10000 rows needed to this step. So a macro will be much easier. – John Jan 16 '20 at 17:48

1 Answers1

0

If you'd prefer to not use cell formulae, a for each loop is a good point to start at, iterating through the sheets in your workbook with an if statement to control sheets to ignore.

Dim ws As Worksheet
Set sum_ws = ActiveWorkbook.Worksheets("summary")
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "summary" Then
        ' rest of your code
    End If
Next ws

Keeping track of which cell you're filling in your summary sheet is a bit more tricky. If the sheets in your workbook will always be in the correct order, you could declare an integer and increment it after each loop and use it to reference the column index:

Dim ws As Worksheet
Dim sum_ws As Worksheet 'summary worksheet
Dim i As Integer

i = 1
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "summary" Then
        sum_ws.Cells(3, i).Value = ws.Range("B3").Value
        i = i + 1
    End If
Next ws

However, if the sheets cannot be guaranteed to be in the correct order, it would be better to explicitly refer to each cell in the summary sheet as a separate range and store them in a collection or dictionary for later use when iterating the sheets.

Dim ws As Worksheet
Dim sum_ws As Worksheet
Dim coll As New Collection

Dim t1 As Range 'team 1 summary cell
Dim t2 As Range 'team 2 summary cell
Dim t3 As Range 'team 3 summary cell

Set sum_ws = ActiveWorkbook.Worksheets("summary")
Set t1 = sum_ws.Range("A2")
Set t2 = sum_ws.Range("B2")
Set t3 = sum_ws.Range("C2")

coll.Add Item:=t1, Key:="team1" 'key same as sheet name
coll.Add Item:=t2, Key:="team2"
coll.Add Item:=t3, Key:="team3"

For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "summary" Then
        coll(ws.Name) = ws.Range("B3").Value
    End If
Next ws

I hope that helps.

GothicAnatomist
  • 146
  • 4
  • 15