VBA Syntax for Passing Multiple Variables Between Subs

[mctopher]

I'm having a problem on what I think is a simple issue - passing variables between subs. I have no problem passing a single variable from one sub to another, however when I try to do this with two I get the error "Run-type error '13': Type mismatch. Could someone help me out with correct syntax? I'm trying to get to where I can just pass workbook name variables over.

Here is what I'm trying to use that is failing:

Sub OpenProdSchedules()

Dim Template As Workbook
Dim ProdSchedule As Workbook

Set Template = ActiveWorkbook

'Delete Prod Schedule worksheet if it already exists

    Application.DisplayAlerts = False
    On Error Resume Next
    ThisWorkbook.Sheets("Prod Schedule").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

'Resume

    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Prod Schedule"
    Range("A1").Value = "Job"
    Range("B1").Value = "Item"
    Range("C1").Value = "Description"
    Range("D1").Value = "Scheduled Completion Date"

MsgBox "Please select the Production Schedule."

FileToOpen = Application.GetOpenFilename _
(Title:="Please choose the PRODUCTION SCHEDULE file to import", _
FileFilter:="Excel Files *.xl* (*.xl*),")

If FileToOpen = False Then
    MsgBox "No file specified, Cancelling process."

    Exit Sub

    Else

    Workbooks.Open Filename:=FileToOpen

    Set ProdSchedule = ActiveWorkbook

End If


'HERE IS WHERE THE CODE ERRORS
Call CopyProdData(Template, ProdSchedule)

End Sub

Sub CopyProdData(Template As Worksheet, ProdSchedule As Worksheet)

Dim StartRow As Long

StartRow = Range("A1048576").End(xlUp).Row + 1

Template.Activate

End Sub

Thanks for your help

 

[Hermanito]

The reason for the type mismatch error in your code is because you declare your variables as workbooks, but your function only takes worksheets.

If you change this line:

Call CopyProdData(Template, ProdSchedule)

into this:

Call CopyProdData(Template.Sheets("Prod Schedule"), ProdSchedule.Sheets("Prod Schedule"))

it might work, although I am not sure that is what you intend to do...

 

[mctopher]

That did it, thanks! I had incorrectly tried to define the variable type again and that must have been causing the issue. Removing it and going your route works.