Code Will Not Execute, Find Glitch?

  • So this code "seems" like it should work, but it's not working (I'm sure you've never heard that before ;) ).


    I've got some workbooks that have cosistent ranges on them:


    [VBA]Worksheets(1).Range("B5:B246")[/VBA]


    I'm trying to copy this range from every single workbook, and place it onto a "Summary" workbook (I thought the copy and offset method was appropriate here).




    [VBA]


    Sub Compile_Indexes()

    Dim wbk1 As Workbook
    Dim wbk2 As Workbook
    Dim indx As Range

    'This is the summary workbook that will compile the ranges
    Set wbk2 = Workbooks("summary")

    'This begins the process of sorting through each of the workbooks that are open

    For Each wbk1 In Workbooks 'Workbooks will refer to all open workbooks,right?
    If Not wbk1.Name = wbk2.Name Then
    'This automates the copying of ranges and pastes them into the summary book
    For Each indx In wbk1.Worksheets(1).Range("B5:B246")
    indx.Copy Destination:=wbk2.Worksheets(1).Range("B:B").Offset(0, 1)
    Next indx
    End If
    Next wbk1
    End Sub[/VBA]


    Got any ideas? I think it's just a quick tweak..


    Benjamin

  • Re: Code Will Not Execute, Find Glitch?


    It looks like (I did not test it) it will end up with the B246 from the last workbook copied throughout Column C of Summary -- does it?


    This (also not tested) should (may) copy B5:B246 of each other workbook to A5:A246, B5:B246, ... of Summary.


    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]