compile error, need to simplify/shorten

[peterlaramore]

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 "Compile Error: 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. . .

 

[Guest]

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

 

[peterlaramore]

You rarely need to select or activate anything in order to work with it.
These lines could be changed from


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

Thanks, I'll try that