How to cycle through control sheet in workbook

[hugo08642]

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:
30424

[Bob Phillips]

You don't have a column named Bigpeer.

[hugo08642]

Whoops, thanks for picking that up. I have changed the column on the right to Bigpeer and I fixed up the code but I still have the same error code for the same line of code:


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("B1:B" & Rows)
Bigpeers = Sheets("Control").Range("E1:E" & 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 Bigpeer In Bigpeers
Set ws = ThisWorkbook.Worksheets(Ticker)
Range("AB1:AL300").Select
Selection.Copy
For Each Ticker In Tickers
Set ws = ThisWorkbook.Worksheets(Bigpeer)
ws.Range("AN1").PasteSpecial
Next Ticker
Next Bigpeer
End Sub

The attached image is the updated control sheet
30426

[Aussiebear]

Actually I have a couple of queries here;

Is there any purpose served in rows 4 & 5 of the code, you define Tickers and Bigpeers as a range, and then three rows down you redefine Tickers & Bigpeers as DataBodyRange.Values. Do we really need both?

My second query is based on the For each Ticker in Tickers loop where it pasteSpecial the value to AN1. Are we not overwriting the value to the same cell?

And lastly, I am wondering about the naming convention here, as it can lead to confusion. Ticker is both the worksheet name, and a cell range within the DataBody Range Tickers. Similarly Bigpeers is dual purposed?


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("B1:B" & Rows)
Bigpeers = Sheets("Control").Range("E1:E" & 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 Bigpeer In Bigpeers
Set ws = ThisWorkbook.Worksheets(Ticker)
Range("AB1:AL300").Select
Selection.Copy
For Each Ticker In Tickers
Set ws = ThisWorkbook.Worksheets(Bigpeer)
ws.Range("AN1").PasteSpecial
Next Ticker
Next Bigpeer
End Sub

[hugo08642]

Hi,

I am pretty new to coding and this naming convention was supplied to me in another post.

For your first query; this was given to me in another post, which resulted in the below code, and it works well (happy to hear if there is a better approach). This is why I tried following the same approach (in Sub Valuations below) for the code I am having trouble with (Sub Peers):


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

Re your second query; no we are not overwriting anything, AN1, and the surrounding cells are blank. I am copying AB1:AL300 from one sheet and pasting to AN1 on another.

Re your third query; as above, that naming convention was suggested in a previous post, for the above code, and it works well. Again, if there are better approaches I would be happy to implement.

Any thoughts on why I am having the error in the Sub Peers, but not the above Sub Valuations?

[Aussiebear]

I'm not questioning why you have the code as such;

But it seems to me (as a beginner) that if you define Tickers as a range in Line 4 of your code and then in line 10 of your code you define it as a value, what was the purpose of initially defining it as a range? You didn't use it as such so it seems as a waste of coding.

Let's look at the second query. Yes I agree you are copying from the current sheet "Control" to the worksheet "Tickers" range "AN1". However as the current code stands For each Ticker in Tickers you do exactly the same thing.... copy to Sheet "Ticker" range "AN1" which must be overwriting the previous data. It appears to me that you need to be advancing the area to be copied to. Your current code simply loops through the next ticker and special pastes to the same cell in the new sheet.

My third query is based purely upon the logic of moving away from any confusion naming items, Objects, Lists etc. In your posts you use Tickers, Ticker, Bigpeers, Bigpeer, within your code, which can be easily confused. Try for example using something like shTickers to clearly define that you refer to the Worksheet "Tickers" as compared to the column "Tickers".

[hugo08642]

Regarding the second query, there must be some confusion here; I am not copying anything from sheet "Control". Also, there is no worksheet "Tickers".

The "Control" sheet contains the names of all worksheets in a table with a header called Ticker (see image above). In this table there are plenty of different tickers, which correspond to the names of the worksheets. All of the different worksheets are named after a different stock ticker.

What happens is the code cycles through the Tickers (in the "Control" sheet) to navigate to the desired worksheet to copy and paste in another worksheet.

I understand your point on lines 4 and 10 but the original assistance I received told me to do that and in the other code it works, so as a newcomer I wasn't too comfortable with tearing something that worked apart.

Do you have any recommendations on how to achieve my goal, even if it involves a completely different approach?

[hugo08642]

Thanks for the assistance, this has been resolved.

[Aussiebear]

@ hugo08642, Actually this thread is not resolved. There is no solution here to your thread. Can you please supply the solution you received from the other Experts?