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:
Thanks for your help
Here is what I'm trying to use that is failing:
HTML:
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
HTML:
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