CODEWITHSUNDEEP
vbaexcel
user8958702
user8958702

Reputation: 57

Fixing a mismatch error within a loop

continuation of my previous question. I think I've made some progress but gotten stuck again:

I've created two loops - one for month to be checked by user. Other will remain hidden but carries location of each file. I'd like it to pick values from the other file ("Training1" in each) and bring it to "2017 Actuals" of current file.

I've tested portions and I think I'm going wrong at the following which gives me a mismatch error, but any tips will be helpful:

Set wks = wkb.Sheets("Training1")

Full code here:

Private Sub UpdateActuals_Click()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.AskToUpdateLinks = False
Dim p As Integer
Dim i As Integer
For i = 1 To 12
    If Me.Controls("Month" & i).Value = True Then
        For p = 1 To 12
        Dim wkb As Workbook
        Dim wks As Workbook
        Set wkb = Workbooks.Open(Me.Controls("Location" & p))
        Set wks = wkb.Sheets("Training1")
        ThisWorkbook.Sheets("2017 Actuals").Range(i + 1, 5) = wks.Range("Start:Finish")
        Next p
    End If
Next i

wkb.Close
Application.DisplayAlerts = True
Application.AskToUpdateLinks = True
Application.ScreenUpdating = True

End Sub

Upvotes: 1

Views: 26

Answers (1)

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

You need to declare your wks as type Worksheet.

So in your block of code, update it to this:

If Me.Controls("Month" & i).Value = True Then
    For p = 1 To 12
    Dim wkb As Workbook
    Dim wks As Worksheet    ' Not Workbook
    Set wkb = Workbooks.Open(Me.Controls("Location" & p))
    Set wks = wkb.Sheets("Training1")
    ThisWorkbook.Sheets("2017 Actuals").Range(i + 1, 5) = wks.Range("Start:Finish")
    Next p
End If

Upvotes: 1

Related Questions