dnicholsby
New Member
- Joined
- Jan 24, 2017
- Messages
- 26
Hello,
I have a spreadsheet that puts a stock into a specific tab depending on its industry. The trouble I am having is that I sometimes have 2000 stocks this has to cycle through and it takes a significant amount of time. If anyone can think of a way to speed it up I would appreciate it. Code is:
I have a spreadsheet that puts a stock into a specific tab depending on its industry. The trouble I am having is that I sometimes have 2000 stocks this has to cycle through and it takes a significant amount of time. If anyone can think of a way to speed it up I would appreciate it. Code is:
Code:
'This section clears the data from the Sector sheetsDim ws As Worksheet
For Each ws In Sheets(Array("Consumer Discretionary", "Consumer Staples", "Energy", "Banks", "Financials excl Banks", "Healthcare", "Industrials", "IT", "Materials", "Real Estate", "Telecoms", "Utilities"))
ws.Activate
range("A16").ClearContents
range("A17").Select
range(Selection, Selection.End(xlDown).End(xlToRight)).ClearContents
Next ws
'This creates a named range [FullStockList] for all stocks in the Stage 1 tab
Sheets("Stage 1").Select
Sheets("Stage 1").range("B7:B" & range("B7").End(xlDown).Row).Name = "FullStockList"
'This allocates each stock into its sector tab
Dim i As Variant
For Each i In [FullStockList]
i.Offset(0, 5).Select
If i.Offset(0, 5) = "Consumer Discretionary" Then
i.Copy
Worksheets("Consumer Discretionary").range("a9999").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
ElseIf i.Offset(0, 5) = "Consumer Staples" Then
i.Copy
Worksheets("Consumer Staples").range("a9999").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
ElseIf i.Offset(0, 5) = "Energy" Then
i.Copy
Worksheets("Energy").range("a9999").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
ElseIf i.Offset(0, 5) = "Financials" And i.Offset(0, 6) = "Banks" Then
i.Copy
Worksheets("Banks").range("a9999").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
ElseIf i.Offset(0, 5) = "Financials" Then
i.Copy
Worksheets("Financials excl Banks").range("a9999").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
ElseIf i.Offset(0, 5) = "Health Care" Then
i.Copy
Worksheets("Healthcare").range("a9999").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
ElseIf i.Offset(0, 5) = "Industrials" Then
i.Copy
Worksheets("Industrials").range("a9999").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
ElseIf i.Offset(0, 5) = "Information Technology" Then
i.Copy
Worksheets("IT").range("a9999").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
ElseIf i.Offset(0, 5) = "Materials" Then
i.Copy
Worksheets("Materials").range("a9999").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
ElseIf i.Offset(0, 5) = "Real Estate" Then
i.Copy
Worksheets("Real Estate").range("a9999").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
ElseIf i.Offset(0, 5) = "Telecommunication Services" Then
i.Copy
Worksheets("Telecoms").range("a9999").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
ElseIf i.Offset(0, 5) = "Utilities" Then
i.Copy
Worksheets("Utilities").range("a9999").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
End If
Next i
Application.ScreenUpdating = True