Excel VBA Not Writing to Summary Sheet

Question

I am trying to write code to copy certain ranges of data from each sheet to a summary sheet. I have attempted many versions of this (far too many for me to put them all here), but with each one the summary sheet has empty values. I don't understand what is going wrong, and I have no theories.

Here is the version that I wrote myself. All of the other variations I tried were found on the Internet:

Note: The actual summary sheet is created elsewhere in code. Also, the reason why I have sheetIndex starting at 6 is because that's the first sheet I want to take data from.

Sub PopulateSummary()
Dim nmbrParts As Integer
Dim partIndex As Integer
Dim sheetIndex As Integer
Dim sheetCount As Integer

sheetCount = ThisWorkbook.Sheets.Count
for sheetIndex = 6 To sheetCount
    With Sheets(sheetIndex)
        .Activate
        .Range("B2").Select
        Call SelectFirstToLastInColumn
        nmbrParts = Selection.Count
        Sheets("Summary").Activate
        for partIndex = 1 To nmbrParts
            row = partIndex + 1

            Sheets("Summary").Cells(row, 1).Value = .Cells(row, 1).Value
            Sheets("Summary").Cells(row, 2).Value = .Cells(row, 2).Value
            Sheets("Summary").Cells(row, 3).Value = .Cells(row, 4).Value
            Sheets("Summary").Cells(row, 3).Value = .Cells(row, 3).Value
            Sheets("Summary").Cells(row, 5).Value = .Cells(row, 6).Value

        Next partIndex
    End With
Next sheetIndex

End Sub

EDIT: I figured something out. Since I'm invoking these macros from another workbook, I changed ThisWorkbook to ActiveWorkbook, so it counts the right number of sheets. The problem isn't solved, though.

Can you run the macro step by step (F8 in the Visual Basic window) and pause on 'nmbrParts = Selection.Count'. Check that the cells are actually selected and if so, proceed down to '.Cells(row, 1).Value' and determine if that value is the value you want. Add a watch to display the value in the Watches window. — George

redOctober13 redOctober13 · Accepted Answer · 2014-07-15T15:33:11

Break it into pieces and step through the code one line at a time. That will tell you whether you're selecting what you think you're selecting, whether row is what you think it is, etc. Also, pause when the code hits your first Sheets("Summary").Cells... line and use the Immediate Window (Ctrl+G) to output the value of your destination cell with

?.Cells(row, 1).Value

Also, since you've got a With block, you don't really need to activate and select cells (and you shouldn't select most of the time in VBA anyway, as doing stuff in the sheet is slow), and you also don't have to activate Sheets("Summary") since you're referencing it directly. You could do something like:

With Sheets(sheetIndex)
    nmbrParts = .Range("B:B").Find(what:="*",searchDirection:=xlPrevious).Row
    for ...
    .
    . 
    .
End With

The simpler and shorter the code, the easier it is to test.

Excel VBA Not Writing to Summary Sheet

2 Answers