L
Legacy 3234
Guest
I'm in over my head again and could use some help.
I have multiple versions of a "Region" workbook. Each version will be copied to a different server. Each version will be copying data from a range in one or more "State" workbooks to be stacked in a range in the "Region" workbook.
Here's the code I've written. As soon as I step into it I get "Compile Error. Type Mismatch." and it highlights StateBook in "Workbooks.Open StateBook". From the help file it seems that dimming this as a variant is causing the problem but I don't know how I should do it instead. Can someone tell me how to fix this?
I have multiple versions of a "Region" workbook. Each version will be copied to a different server. Each version will be copying data from a range in one or more "State" workbooks to be stacked in a range in the "Region" workbook.
Here's the code I've written. As soon as I step into it I get "Compile Error. Type Mismatch." and it highlights StateBook in "Workbooks.Open StateBook". From the help file it seems that dimming this as a variant is causing the problem but I don't know how I should do it instead. Can someone tell me how to fix this?
Code:
Sub CopyPremiumData()
RegionBook = ActiveWorkbook.Name
Dim StateBook(3) As Variant
StateBook(0) = "Test1 Model.xls"
StateBook(1) = "Test2 Model.xls"
StateBook(2) = "Test3 Model.xls"
Dim x As Integer
Application.DisplayAlerts = False
Application.ScreenUpdating = False
RegionBook.Activate
ChDir ThisWorkbook.Path
Sheets("PremData").Visible = True
Application.Goto Reference:="PremiumDelete"
Selection.ClearContents
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
For x = 1 To 3
RegionBook.Activate
Application.Goto Reference:="PremInStart"
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
On Error GoTo ErrorCheck
Workbooks.Open StateBook ''''''This is where the type mismatch error is
On Error GoTo 0
StateBook.Activate
Calculate
Sheets("BPMData").Visible = True
Application.Goto Reference:="DataOut"
Selection.Copy
RegionBook.Activate
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
StateBook.Activate
ActiveWorkbook.Close
Next x
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
RegionBook.Activate
Sheets("PremData").Visible = False
Exit Sub
ErrorCheck:
MsgBox "All workbooks must be in the same directory."
Exit Sub
RegionBook.Activate
Sheets("PremData").Visible = False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub