Question: Best way to Create VBA Sequential Range Loop

[captainxcel]

Hi everyone: I've been running a macro which pulls in historical stock price data for a series of tickers (range names of tick1, tick2, tick3, etc.). I've yet to combine the 'fetcher' workbook into the destination workbook, but no matter. What I'd really like to do is condense the code into a loop so that I can make the code more efficient. Right now I have the code set up sequentially with a procedure for each ticker as below. Anything else you see as well to make the code more efficient. Thanks in advance!

' Ticker 1 Procedure
Windows("HistData.xlsm").Activate
Application.Goto Reference:="tick1"
Application.CutCopyMode = False
Selection.Copy
Windows("PriceFetcher.xlsm").Activate
Application.Goto Reference:="ticker"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.Wait (Now + TimeValue("0:00:01"))
Application.Goto Reference:="PRICE_RANGE"
Application.CutCopyMode = False
Selection.Copy
Windows("HistData.xlsm").Activate
Application.Goto Reference:="root1"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'
' Ticker 2 Procedure
Windows("HistData.xlsm").Activate
Application.Goto Reference:="tick2"
Application.CutCopyMode = False
Selection.Copy
Windows("PriceFetcher.xlsm").Activate
Application.Goto Reference:="ticker"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.Wait (Now + TimeValue("0:00:01"))
Application.Goto Reference:="PRICE_RANGE"
Application.CutCopyMode = False
Selection.Copy
Windows("HistData.xlsm").Activate
Application.Goto Reference:="root2"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

 

[Norie]

Perhaps something like this.

Dim I As Long
Dim NoTickers As Long

    NoTickers = 2
    
    For I = 1 To NoTickers
        Workbooks("HistData.xlsm").ActiveSheet.Range("tick" & I).Copy
        Workbooks("PriceFetcher.xlsm").ActiveSheet.Range("ticker").PasteSpecial Paste:=xlPasteValues
        Application.Wait (Now + TimeValue("0:00:01"))
        Workbooks("PriceFetcher.xlsm").ActiveSheet.Range("PRICE_RANGE").Copy
        Workbooks("HistData.xlsm").ActiveSheet.Range("root" & I).PasteSpecial Paste:=xlPasteValues
    Next I

Note, I'm assuming tickx, rootx and PRICE_RANGE are named ranges on the active sheet in the relevant workbooks.

 

[shg]

Similarly,

Sub CaptXL()
  Dim wksHist       As Worksheet
  Dim wksPFet       As Worksheet
  Dim i             As Long

  Set wksHist = Workbooks("HistData.xlsm").Worksheets("Sheet1")      ' change sheet name as appropriate
  Set wksPFet = Workbooks("PriceFetcher.xlsm").Worksheets("Sheet1")  ' change sheet name as appropriate

  For i = 1 To 2
    wksPFet.Range("ticker").Value2 = wksHist.Range("tick" & i).Value2
    Application.Wait (Now + TimeValue("0:00:01"))
    wksHist.Range("root" & i).Value2 = wksPFet.Range("Price_Range").Value2
  Next i
End Sub

 

[captainxcel]

Thanks shg and Norie! This approach did work and has made the code shorter and cleaner. I used Norie's but updated the code with shg's suggestion to Dim the worksheet names as well, in case that code is copied into another workbook. Very elegant! Thanks again.