CODEWITHSUNDEEP
excelvba
jules325
jules325

Reputation: 179

Loop not Inserting Values on to a Summary Sheet

Trying to compile one list from 300 worksheets that are identically formatted. I want to paste/insert the values on this summary sheet so that after each iteration of the loop, the new data is placed in the next empty row, so that there is one single "Master List". I'm getting a "type mismatch" error on the "Summary.Cells" line, 4th from the bottom.

Sub Compile()

Dim ws as Worksheet
Dim Summary as Worksheet

Set Summary = ThisWorkbook.Sheets("Sheet1")

For Each ws in ThisWorkbook.Sheets

If Not ws.Name = "Summary" Then

Summary.Cells(Rows.Count,1).End(xlUp).Row+1 = ws.Range("BB1:DO11").Value

End If

Next ws

End Sub

Any help would be greatly appreciated, thanks,

Upvotes: 0

Views: 35

Answers (2)

SJR
SJR

Reputation: 23081

This is virtually the same as Scott's answer so if you are good enough to accept one, please accept his, but just shows a slightly different way of resizing the range.

Sub Compile()

Dim ws As Worksheet
Dim Summary As Worksheet

Set Summary = ThisWorkbook.Sheets("Sheet1")

For Each ws In ThisWorkbook.Sheets
    If Not ws.Name = "Sheet1" Then  'I think this is right but may need to be 'Summary'
        With ws.Range("BB1:DO11")
            Summary.Cells(Rows.Count, 1).End(xlUp)(2).Resize(.Rows.Count, .Columns.Count).Value = .Value
        End With
    End If
Next ws

End Sub

Upvotes: 3

Scott Craner
Scott Craner

Reputation: 152650

When assigning the values directly the ranges must be the same size.

You are trying to equate a row number to an array.

Summary.Cells(Rows.Count,1).End(xlUp).Row+1

returns a row number as a Long not a Range.

To get the first cell we use, as per SJR's comment:

Summary.Cells(Rows.Count, 1).End(xlUp).Offset(1)

Then we need to resize that to the same size as the input range.

Summary.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(11, 66).Value

use 

Sub Compile()

Dim ws As Worksheet
Dim Summary As Worksheet

Set Summary = ThisWorkbook.Sheets("Sheet1") 'This does not match the skipped sheet name.

For Each ws In ThisWorkbook.Sheets
    If Not ws.Name = "Summary" Then 'Should this be "Sheet1" or Summary.Name
        Summary.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(11, 66).Value = ws.Range("BB1:DO11").Value
    End If
Next ws

End Sub

Upvotes: 3

Related Questions