VBA dynamic loop macro

Question

I am trying to build a loop macro is visual basic which takes cells from a range in one worksheet and places them in to specific cells in other worksheet directed by the worksheet's tab name in a parallel range. The ranges need to be variable - with more or less data as directed.

This is the code which currently works for one cell:

Sub Ticker_input()

    Dim wsname As String

    wsname = ActiveCell.Value

    Worksheets("Summary").Range("Tab_name").Select
    Worksheets(wsname).Range("CapIQ_ticker").Value = ActiveCell.Offset(0, 1)

End Sub

I need to turn this into a loop macro.

Help would be greatly appreciated.

I gave an example below of looping through a named range, but you may also be able to do this with a formula by looking at the requirements - hard to tell without examples of the data. — Kevin
– Kevin, Commented Nov 25, 2014 at 12:31

BobbitWormJoe · Accepted Answer · 2014-11-25 12:26:49Z

1

There are multiple ways to do loops in VBA. This is one way that could apply to your situation:

Sub Ticker_input()

    On Error Resume Next
    Application.ScreenUpdating = False

    Dim wsname As String
    Dim rTabNames As Range, c As Range

    Set rTabNames = Worksheets("Summary").Range("Tab_name")

    For Each c In rTabNames
        If c.Value <> "" Then
            wsname = c.Value
            Worksheets(wsname).Range("CapIQ_ticker").Value = c.Offset(0, 1).Value
        End If
    Next

    Application.ScreenUpdating = True

End Sub

Just make sure your named range "Tab_name" is the entire range of cells that could contain sheet names (for example, if you list your sheet names in A, have "Tab_name" be referring to that entire column), as opposed to one cell.

answered Nov 25, 2014 at 12:26

BobbitWormJoe

6394 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alastair Matchett Over a year ago

Thanks so much - the additional problem is the range is dynamic, in other words it can be larger and smaller - is there a way of counting down the list?

BobbitWormJoe Over a year ago

That's no problem. Just have your "Tab_name" range refer to the maximum size the range could be (possibly the entire column). The loop ignores blank values using If c.Value <> "" Then so it doesn't matter if the named range is larger than the data it contains.

Kevin · Accepted Answer · 2014-11-25 12:25:48Z

I'm not exactly sure what you are trying to accomplish, but below is an example of looping through a named range. It is dnyamic in the fact that it will loop through the named range and if you reset the size of the named range it will only loop through those cells.

~ hope this helps.

Sub loopRnage()

'create your variable outside the loop
Dim wsname

'tell the loop the range you want to loop through
For Each cell In ThisWorkbook.Sheets("Stocks").Range("symbols")

    'set the variable to the current cell
    wsname = cell.Value
    Worksheets("Summary").Range("Tab_name").Select
    'use the variable to set the target name
    Worksheets(wsname).Range("CapIQ_ticker").Value = cell.Offset(0, 1)

Next cell

End Sub

Collectives™ on Stack Overflow

VBA dynamic loop macro

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related