compile error, need to simplify/shorten
On Nov 9, 11:17 pm, JMB wrote:
You rarely need to select or activate anything in order to work with it. These lines could be changed from Range("B2").Select Selection.Copy Sheets("Sheet2").Select Range("A1").Select ActiveSheet.Paste ActiveSheet.Paste Application.CutCopyMode = False To Sheets("Sheet1").Range("B2").Copy Sheets("Sheet2").Range("A1") It appears you are copying values from Sheet1 column B to Sheet2 A1, refreshing a querytable in Sheet2 A2, then copying A2 back to Sheet1 into column M. Maybe something similar to this is what you are looking for: Sub test() Dim rngSource As Range Dim rngDest As Range Dim rngQuery As Range Dim rngCell As Range Set rngSource = Sheets("Sheet1").Range("B2:B3") '<CHANGE Set rngDest = Sheets("Sheet2").Range("A1") Set rngQuery = Sheets("Sheet2").Range("A2") For Each rngCell In rngSource.Cells rngCell.Copy rngDest With rngQuery .QueryTable.Refresh BackgroundQuery:=False .Copy rngSource.Parent.Range("M" & rngCell.Row) End With Next rngCell End Sub " wrote: I know this isn't the best way to go about this, but I a learning here. . . I am using this to update stock prices from a Google Finance query. I have a list of about 200 stocks, and when I run the below query for 200 cells I get a "CompileError: Procedure too large". Can anybody show me the light simplifying this? Thanks much. Range("B2").Select Selection.Copy Sheets("Sheet2").Select Range("A1").Select ActiveSheet.Paste ActiveSheet.Paste Application.CutCopyMode = False Range("A2").Select Selection.QueryTable.Refresh BackgroundQuery:=False Selection.Copy Sheets("Sheet1").Select Range("M2").Select ActiveSheet.Paste ActiveSheet.Paste Application.CutCopyMode = False Range("B3").Select Selection.Copy Sheets("Sheet2").Select Range("A1").Select ActiveSheet.Paste ActiveSheet.Paste Application.CutCopyMode = False Range("A2").Select Selection.QueryTable.Refresh BackgroundQuery:=False Selection.Copy Sheets("Sheet1").Select Range("M3").Select ActiveSheet.Paste ActiveSheet.Paste Application.CutCopyMode = False And so on. . . Thanks, I'll try that |
All times are GMT +1. The time now is 11:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com