vba - iterate over collection

Question

I want to copy paste some columns of a closed workbook into my active workbook. My columns are separeted, so I want to do that with a loop over a Collection variable.

here my code (EDIT)

Option Explicit

Sub CopyDataFromClosedWbk()

    'copy data from closed workbook to active workbook
    Dim colonne As Collection
    Dim col As Variant
    Dim xlApp As Application
    Dim xlBook As Workbook
    Dim Sh As Object

    Set colonne = New Collection

    colonne.Add "A:B", "A1"
    colonne.Add "E:F", "G1"
    colonne.Add "N", "I1"
    colonne.Add "P", "F1"


    Set xlApp = CreateObject("Excel.Application")


    'Path source workbook
    Set xlBook = xlApp.Workbooks.Open("C:\Users\Amira AYADI\Desktop\Stage\Automatisation\Base Case_BDD CAPACITAIRE_ENVOYE_V2 2017_2023_24042017.xlsx")
    xlBook.Sheets("DATA").Range("A1:CJ374810").AutoFilter
    xlBook.Sheets("DATA").Range("BD1").Select
    xlBook.Sheets("DATA").Range("$A$1:$CJ$374810").AutoFilter Field:=56, Criteria1:="CMR"

    For Each col In colonne

        xlBook.Sheets("DATA").Range(col).Copy
        xlApp.DisplayAlerts = False
        Debug.Print col
        Set xlBook = Nothing
        Set xlApp = Nothing
        Set xlBook = ActiveWorkbook
        Set Sh = xlBook.Sheets("Calcul")
        'Sh.Activate
        Range(colonne.Item(col)).Select
        Sh.paste


    Next col

    xlBook.Close
    xlApp.Quit


End Sub

But that don't work and there are several mistakes:

First the

Range(colonne.Item(col)).Select

don't work whith "col" but with index I guess. So how can iterate over Key's value ?

Second when I remplace col by 1 like that (for a try) :

Range(colonne.Item(1)).Select

I have a 91 error : Object variable or With block not set.

In addition to that, I have a pop up window saying that the workbook "source.xlsx" is ready to modifications. How can I desable this?

Do you have any ideas ?

Dont use `.Select`. Also you are opening and closing the workboom after every cell, thats not very efficient. — Plagon, Jun 18 '17 at 15:46
I did not pay attention, Thanks. If I don't use .select how I do it ? I tried Range(colonne.Item(col)).Paste but that did'nt work — blabla, Jun 18 '17 at 16:04
You need to use Scripting.Dictionary here in place of the Collection, so you can access both the keys and the values. https://stackoverflow.com/questions/5702362/vba-collection-list-of-keys — Tim Williams, Jun 18 '17 at 16:33

score 0 · Accepted Answer · answered Jun 18 '17 at 17:01

Try this out:

Option Explicit

Sub CopyDataFromClosedWbk()

    Dim xlApp As Application
    Dim xlBook As Workbook
    Dim sht As Worksheet, sht2 As Worksheet

    Set sht2 = ThisWorkbook.Sheets("Calcul")
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Open("C:\Users\User\Desktop\source.xlsx") 'Adjust
    Set sht = xlBook.Worksheets("DATA")
    xlApp.DisplayAlerts = False

    sht.Range("A:CJ").AutoFilter Field:=56, Criteria1:="CMR"
    sht.Range("A:B").Copy: sht2.Range("A1").PasteSpecial xlPasteValues
    sht.Range("E:F").Copy: sht2.Range("G1").PasteSpecial xlPasteValues
    sht.Range("N:N").Copy: sht2.Range("I1").PasteSpecial xlPasteValues
    sht.Range("P:P").Copy: sht2.Range("F1").PasteSpecial xlPasteValues

    xlBook.Close
    xlApp.Quit
End Sub

I dont know if theres any reason behind using a collection/dictionary to save the columns but its much easier this way.

vba - iterate over collection

1 Answers1