none
VBA Macro RRS feed

  • Question

  • I have what I think will probably be a simple question. I have created a macro that opens some files and pastes into some other files. There is a file for each state that I want to open, and after some other tasks, paste into a master file. I don't want to copy and paste this code for each state, so I wanted to make it so that I could call this macro again and again for each state. The state abbreviation is in the file name. So say i have a file called AK_Tables, MI_Tables, TX_Tables, and I want to call all of these and then the rest of the programming will be the same. 

    Sub PasteTables()

    Workbooks.Open Filename:= "R:\Project\Project Name\AK_Tables.xlsx"

    Code...

    Windows("AK_Tables.xlsx").Activate

    Code...

    End Sub

    How could I edit this code so that instead of AK I would have a variable, and I could call the macro over and over with the variable set to AK, MI, TX, etc.

    For sas this would be equivalent to :

    %macro pastetables(st=);

    Workbooks.Open Filename:= "R:\Project\Project Name\&st._Tables.xlsx"

    Code

    %mend;

    %pastetables(st=AK)

    %pastetables(st=MI)

    %pastetables(st=TX)

    Friday, October 26, 2012 5:23 PM

All replies

  • Try something like this:

    Sub PasteTables(st As String)
        Dim wbk As Workbook
        Set wbk = Workbooks.Open(Filename:="R:\Project\Project Name\" & st & "_Tables.xlsx"
        ...
        wbk.Activate
        ...
    End Sub

    Call like this:

    Sub Test()
        PasteTables "AK"
        PasteTables "MI"
        PasteTables "TX"
    End Sub


    Regards, Hans Vogelaar

    Friday, October 26, 2012 7:49 PM