It is very difficult to guess what your code is trying to do. This answer is more about good coding practice than specific recommendations. My hope is this will allow you to correct your own code.
ActiveCell.Offset(0, 1).Select serves no purpose. The ActiveCell is the cell containing the cursor. You do not position the cursor at the beginning of the routine to a first cell and you neither read from nor write to the ActiveCell during the rest of the macro.
For Each ws In ThisWorkbook.Worksheets serves no purpose since you do not use ws. In For Each i In ThisWorkbook.ActiveSheet.Range("G2:H200") did you mean to write For Each i In ws.Range("G2:H200")? I cannot see anywhere else you might have meant to use ws.
Each Cell In Range("A24:A224") references the ActiveSheet. This is whatever worksheet happens to be active when the macro was started. My guess is that Range("A24:A224") is in the summary worksheet. If the user happens to be looking at another worksheet when they start the macro, that other worksheet is the ActiveSheet and not the summary worksheet. I assume you want to accumulate totals in the summary worksheet. If the wrong worksheet is active when the macro is started, that worksheet will be corrupted beyond recovery. UnDo cannot undo what a macro has done so you will have to revert to the previous copy of the workbook. I hope you create a new copy of the workbook before trying experimental macros so you can revert to an undamaged version. You need something like:
Dim WshtSumm As Worksheet
Set WshtSumm = Worksheets("Summary")
For Each Cell In WshtSumm.Range("A24:A224")
: : : : : : :
Next
In my opinion, you should not use names like Cell because it is too similar to the reserved word Cells. You should not use names like i because such names are meaningless. Perhaps it does not matter in such a small macro but, as your macros get bigger, meaningful names will be a real help when you or someone else looks at this macro in six or twelve months. Few macros last unchanged for ever; they are updated every few months to address changing requirements. I do not know what Cell and i represent but perhaps CellSumm and CellData would be better names.
Consider ThisWorkbook.Worksheets and Range("A24:A224"). I have just told you that you should have identified which worksheet holds Range("A24:A224"). This was because your workbook holds several worksheets any of which could be active. If you have a workbook that has only one worksheet, Range("A24:A224") might be OK. Personally, I avoid unqualified ranges because it is so easy to add a new worksheet. However, is it likely that you will have two workbooks or more workbooks open? Yes, it is possible to run a macro in one workbook from another workbook but is this likely? ThisWorkbook identifies the workbook holding the macro. If you are concerned that the wrong workbook will be active, you are absolutely correct to specify which collection of worksheets you wish to access because each workbook has its own collection. However, Worksheets on its own will normally be sufficient. This is something you need to think about, particularly as your macros get more complicated: do I need to qualify this range or worksheet or anything of which you might have more than one?
Consider:
For Each ws In ThisWorkbook.Worksheets
For Each i In ws.Range("G2:H200")
I have not qualified ws because a worksheet has a property Parent which identifies its workbook. You qualified ws when you created it.
One of the worksheets in ThisWorkbook.Worksheets will be the summary worksheet. Do you want to examine cells in the summary worksheet? I assume not. You need something like:
Dim WshtSumm As Worksheet
Dim WshtOther As Worksheet
Set WshtSumm = Worksheets("Summary")
For Each WshtOther In Worksheets
If WshtOther.Name <> WshtSumm.Name Then
: : : : : : :
End If
Next
This code only examines worksheets whose name does not match the summary worksheet's name. Whenever you look through an entire collection of something, you need to ask yourself: do I really want to look at everyone?
Consider:
If Cell.Value = i.Value Then
ActiveCell.Offset(0, 1).Select
Cell.Value = Cell.Value + i.Value
End If
Where you have written ActiveCell, I suspect you meant Cell giving:
If Cell.Value = i.Value Then
Cell.Offset(0, 1).Select
Cell.Value = Cell.Value + i.Value
End If
This would make a little more sense but not much. You search Range("G2:H200") of every worksheet for a value that matches a value in Range("A24:A224") of the summary worksheet. If you find that matching value you add it to the next cell in Range("A24:A224"). You tell us nothing about the summary worksheet or the data worksheets so this may be sensible but it does not feel sensible. If you find a match on "A30", you add the value of "A30" to "A31". But then you search for a match on the amended "A31". I cannot imagine any requirement for which this would be sensible.
Even if I thought this was sensible, I don’t think it will work. If I write:
For X = 1 to 10
: :
Next
I cannot change the value of X within the For Loop. I have not tried recently but my recollection is that the attempt to change X is ignored. You are trying to change the equivalent of X for a For Each statement. I have not tried but I suspect your attempt to change Cell will fail. Even if it can change Cell you should not. Select is a slow command and you should only use it if it is essential. Try something like:
If Cell.Value = i.Value Then
Cell.Offset(0, 1).Value = Cell.Offset(0, 1).Value + i.Value
End If
I hope the above helps. If it does not, please provide details of the two ranges so I can better understand what you are trying to do.
How do I SUM a variable range between two variable start and end , I have a worksheet which is solely for reporting - it is summing and displaying data from other worksheets and workbooks. I have several drop� Figure 4. Applying the SUMIF formula across multiple sheets. How this Formula Works. We use the named range of multiple sheets to create a reference for each sheet and merge these sheets’ names with identical ranges across these multiple sheets. The INDIRECT function is used to identify these sheets names and identical ranges correctly. As
I just tried this and it worked perfect.
Sub GenerateTheFormula()
Dim x, Formula
Formula = "=SUM(" 'Formula begins with =SUM(
For x = 3 To Sheets.Count
Formula = Formula & Sheets(x).Name & "!A1," 'Add SheetName and Cell and Comma
Next x
Formula = Left(Formula, Len(Formula) - 1) & ")" 'Remove trailing comma and add parenthesis
Range("B1").Formula = Formula 'Where do you want to put this formula?
End Sub
Idea from here:
Excel VBA Sum from Multiple Sheets
Using variable to sum multiple sheets, All I need to consolidate a variable number of sheets in a workbook. Each user workbook will have a different number of sheets to sum. I create� Sometimes we need to access different values from different worksheets of the same excel book. Here we are accessing it to add multiple cells in Excel 2016. In this article, we will learn how to sum the values located on different sheets in excel 2016. We will use the SUM function to add numbers. SUM function adds up the values.
