For i loop functions on VBA gives "type mismatch" compiler error

Question

I am getting a compile error 'type mismatch' on my loop function below. I am trying to combine multiple worksheets in a master worksheet and it would help to have a loop function through them. Any help would be appreciated.

Thanks,

Public Sub combine2()
    Dim i As Worksheet
    For i = 1 To Worksheet.Count

        Worksheets(i).Select
        Columns("A:B").Select
        Selection.copy
        Sheets("sheet").Select
        Selection.End(xlDown).Select
        Selection.End(xlUp).Select
        ActiveSheet.Paste
        Range("A1").Select
        Selection.End(xlDown).Select
        Selection.End(xlDown).Select
        Selection.End(xlUp).Select

    Next i
End Sub

Try changing the first two lines to Dim i As Integer and For i = 1 To Worksheets.Count and see if that helps. — jpw
– jpw, Commented Dec 25, 2017 at 2:15

Variatus · Accepted Answer · 2017-12-25 03:47:38Z

2

The code below will copy everything from columns A and B (except the captions) of all sheets (except Sheet1) to the first worksheet in the workbook. You may have to tweak it a little to do what you actually want.

Public Sub combine2()

    Dim Master As Worksheet
    Dim Ws As Worksheet
    Dim Rng As Range
    Dim Rl As Long
    Dim i As Integer

    Set Master = Worksheets(1)
    Application.ScreenUpdating = False
    For i = 2 To Worksheet.Count
        Set Ws = Worksheets(i)
        With Ws
            Rl = .Cells(.Rows.Count, "A").End(xlUp).Row
            Set Rng = Range(.Cells(2, "A"), .Cells(Rl, "B"))
            Rng.Copy
        End With

        With Master
            Rl = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
            .Cells(Rl, "A").PasteSpecial xlValues
        End With
    Next i
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
End Sub

answered Dec 25, 2017 at 3:47

Variatus

14.4k2 gold badges16 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Heriberto Lugo Over a year ago

i upvoted for answering the general question, instead of the error question (as i did)

Heriberto Lugo · Accepted Answer · 2017-12-25 05:34:28Z

2

well in the first line you declare the variable "i" as a object of type Worksheet, then you go and assign it a value of a number. Which you can't do because you specifically said it was a Worksheet - not a number type (such as Integer). That is a type mismatch.

And also it should be worksheets - not worksheet

Dim i As Integer
For i = 1 To Worksheets.Count

edited Dec 25, 2017 at 5:34

answered Dec 25, 2017 at 3:30

Heriberto Lugo

6431 gold badge11 silver badges22 bronze badges

Collectives™ on Stack Overflow

For i loop functions on VBA gives "type mismatch" compiler error

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related