Consulting

Results 1 to 9 of 9

Thread: How to cycle through control sheet in workbook

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1

    How to cycle through control sheet in workbook

    Hi everyone,

    I am having trouble with some code and I was hoping someone could help.

    The following code works well and cycles through a control sheet that has the names of all my worksheets and pastes a valuation template in all worksheets (each worksheet contains financial information for a company):

    Sub Valuations()
    Dim Tickers As Variant, Ticker As Variant, Rows As Long
    Rows = Sheets("Control").Cells(Sheets("Control").Rows.Count, "DA").End(xlUp).Row
    Tickers = Sheets("Control").Range("B1:B" & Rows)
    Dim ws As Worksheet
    Tickers = Sheets("Control").ListObjects("Tickers").ListColumns("Ticker").DataBodyRange.Value
    Sheets("Template").Select
    Range("B1:P300").Select
    Selection.Copy
    For Each Ticker In Tickers
         Set ws = ThisWorkbook.Worksheets(Ticker)
         ws.Range("B1").PasteSpecial
    Next Ticker
    End Sub
    I was hoping I could adapt this code to cycle through the competitors of each company and post financial information in relevant areas. However, I am having trouble. The code currently looks like:

    Sub Peers()
    Dim Tickers As Variant, Ticker As Variant, Bigpeers As Variant, Bigpeer As Variant, Rows As Long
    Rows = Sheets("Control").Cells(Sheets("Control").Rows.Count, "DA").End(xlUp).Row
    Tickers = Sheets("Control").Range("E1:E" & Rows)
    Bigpeer = Sheets("Control").Range("B1:B" & Rows)
    Dim ws As Worksheet
    Set ch = New Selenium.ChromeDriver
    Set ks = New Selenium.Keys
    Bigpeers = Sheets("Control").ListObjects("Bigpeers").ListColumns("Bigpeer").DataBodyRange.Value
    Tickers = Sheets("Control").ListObjects("Tickers").ListColumns("Ticker").DataBodyRange.Value
    For Each Ticker In Tickers
      Set ws = ThisWorkbook.Worksheets(Ticker)
      Range("AB1:AL300").Select
      Selection.Copy
        For Each Bigpeer In Bigpeers
            Set ws = ThisWorkbook.Worksheets(Bigpeer)
            ws.Range("AN1").PasteSpecial
        Next Bigpeer
    Next Ticker
    End Sub
    I am currently getting a run-time error 9: subscript out of range for the following part of the code:

    Bigpeers = Sheets("Control").ListObjects("Bigpeers").ListColumns("Bigpeer").DataBodyRange.Value
    I thought I had declared everything. Any thoughts team?

    The attached image is a snapshot of the control sheet:
    control.jpg
    Last edited by Aussiebear; 01-05-2023 at 08:05 PM. Reason: Reduced whitespace in submitted code

Posting Permissions

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