Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Jul 2006
    Thanked 0 Times in 0 Posts
    Iím attempting to encourage an Excel macro to repeat a block of code about fifty times by using a For Next loop.

    I need to
    Copy and paste a set of headers from one workbook
    Extract data from a different workbook using AutoFilter and selecting visible rows. This part is causing no difficulty.

    I have a table of about fifty rows with a list of named ranges in column A. Column B specifies which column to use to filter (Column 17 is a postal code or Column 22 is a workgroup ID if the group is scattered geographically). Column C specifies the criterion to use (the exact postal code or workgroup ID.).

    Some rows have a named range in Column A but blank cells in B and C.

    What I want the macro to do is to go through the fifty rows,
    copy the first named range,
    paste it at A1 in a new workbook,
    move to the last used row plus 1,

    then to run Autofilter on the large table,
    copy the selected cells
    paste the selection at the end of the new workbook,
    move to the last used row plus 1,

    loop to the next named range and repeat.

    When the cells in Columns B and C are blank, I want the macro to process the named range, skip the large table, and loop to the next named range.

    Is the simplest method to use variable names for Items A, B, and C, which change with every row?

    If I do that, how do I code the macro to update the variable for B and C when A changes?

    Many thanks,


  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Thanked 1,055 Times in 983 Posts
    As you know where the table is I would wander down the table and read the values directly into the variables you use.
    Start by Selecting the first cell in the table, then use Offset to collect the values down and across - you could set the first cell to a variable, but I have always selected.
    var = selection.offset(0,1).value
    set obj = range("A1")
    var = obj.address.offset(0,1).value
    Don't quote me on the second bit.

    cheers, Paul

Posting Permissions

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