| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| help with this error-Compile error: cant find project or library | Excel Discussion (Misc queries) | |||
| simplify procedure to get rid of 0 error values in a spreadsheet | Excel Worksheet Functions | |||
| VBAProject name compile error, not defined at compile time | Excel Programming | |||
| error message: compile error, argument not optional | Excel Programming | |||
Linear Mode
