Copying from Multiple Workbooks into One

[Legacy 3234]

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?

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

 

[Andrew Poulsom]

I think it should be:

Workbooks.Open StateBook(x)

and your assignment statements should be:

Dim StateBook(1 To 3) As Variant

StateBook(1) = "Test1 Model.xls"
StateBook(2) = "Test2 Model.xls"
StateBook(3) = "Test3 Model.xls"

 

[Legacy 3234]

Thanks, that helps. I can step through it now. But I've got another error I don't know what to do with. I'm getting "Runtime error 424, Object Required" when it hits the first "RegionBook.Activate". The help file on that one is totally confusing to me.

 

[Andrew Poulsom]

Instead of:

RegionBook = ActiveWorkbook.Name ' a string variable

use:

Set RegionBook = ActiveWorkbook ' an object variable

Or instead of:

RegionBook.Activate

use

Workbooks(RegionBook).Activate

Currently you trying to us the Activate method with a string, but an object is required. I agree the Help isn't helpful. Trust me, you'll be able to make sense of it eventually.

 

[Legacy 3234]

That did it. Thank you sooo much, Andrew!