VBA dynamic loop macro

0
votes

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.

2
vbaexcel
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

2 Answers

1
votes

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.

0
votes

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