compile error, need to simplify/shorten

P

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. . .
 
J

JMB

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
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need Help with a VBA subroutine 0
Help me5 1
Macro Loop 0
Macro to fill to next field with data 4
Different Results from the Same Macro 3
Help me4 2
Auto Fill Columns A and B with varying ranges 6
Auto fill Sheets 1

Top