CODEWITHSUNDEEP
excelvba
Steve
Steve

Reputation: 1

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

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

Upvotes: 0

Views: 555

Answers (2)

Heriberto Lugo
Heriberto Lugo

Reputation: 643

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

Upvotes: 2

Variatus
Variatus

Reputation: 14373

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

Upvotes: 2

Related Questions