CODEWITHSUNDEEP
excelexcel-2013vba
Jon
Jon

Reputation: 3

Excel VBA Type Mismatch Error 13

I'm kind of new to VBA, and I am having some problems working with multiple workbooks at once. I had a code fully functional that essentially just takes data from one sheet to another given certain conditions are met.

Now I am trying to update the code to move from one workbook to another, and thus I have begun to declare workbooks instead of just referencing sheet names.

Now I am getting various errors.

code follows.

before (worked)

Option Explicit
Sub TrendDataByDay()

   'determine date of data pulled
    Dim CurrentDate As String

    CurrentDate = Sheets("Daily Pull").Range("B23").Value

after (fails; now using 2 workbooks)

Option Explicit
Sub TrendDataByDay()
    Dim wbDaily, wbHistory As Workbook

    'declare daily and history workbooks
    Set wbDaily = ActiveWorkbook
    Workbooks.Open Filename:="\\Daily Focus Metrics\Focus Metrics History by Facility.xlsm"
    Set wbHistory = Application.Workbooks("Focus Metrics History by Facility.xlsm")

    'determine date of data pulled
    Dim CurrentDate As String

    'error here!
    CurrentDate = Workbooks(wbDaily).Sheets("Daily Pull").Range("B23").Value

I also tried:

CurrentDate = wbDaily.Sheets("Daily Pull").Range("B23").Value

I need to understand why I cannot simply declare these workbooks and specify the workbook that a sheet belongs to in this way. I would also like to know the ideal way to reference and dim workbooks without having to "select" each workbook when copying or pasting data.

Upvotes: 0

Views: 227

Answers (1)

Slai
Slai

Reputation: 22866

Change Workbooks(wbDaily) to wbDaily

Also a small note .. unlike VB.Net

Dim wbDaily, wbHistory As Workbook

Is the same as 

Dim wbDaily As Variant
Dim wbHistory As Workbook

so you can change it to 

Dim wbDaily As Workbook, wbHistory As Workbook

Upvotes: 1

Related Questions