Copying from Multiple Workbooks into One

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Copying from Multiple Workbooks into One

  1. #1

    Join Date
    May 2002
    Posts
    121
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Copying from Multiple Workbooks into One

     
    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?

    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

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying from Multiple Workbooks into One

    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"

  3. #3

    Join Date
    May 2002
    Posts
    121
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying from Multiple Workbooks into One

    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.

  4. #4
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying from Multiple Workbooks into One

    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.

  5. #5

    Join Date
    May 2002
    Posts
    121
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copying from Multiple Workbooks into One

      
    That did it. Thank you sooo much, Andrew!

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com