[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel VBA - Copy Web Query Data Once Refresh Is Complete

Posted on 2012-09-19
2
Medium Priority
?
1,129 Views
Last Modified: 2012-10-02
I need the attached file to do the following:

1. Automatically refresh the data from the web query to Sheet1!A1:C17.
2. As soon as the web query refreshed, insert cells shifting existing data down in Range F3:U3.
3. Copy Range A2, and paste in Range F3.
4. Copy Range B4:B17, paste transposed into Range H3:U3
5. Update Chart 2 all data series to reflect last updated data for all series to reflect latest data and previous 49 rows of historical data.

Obviously more work needs to be done on this before it is a finished product, but I am stuck at this point. Your assistance will be greatly appreciated.
ERCOT-Price-Query---Copy.xlsm
0
Comment
Question by:TravisBickle2
2 Comments
 
LVL 18

Accepted Solution

by:
Steven Harris earned 2000 total points
ID: 38447811
Travis,

Try this out:

Sub Scrape_Flip_and_Save()
'
' Scrape_Flip_and_Save Macro
'

'
    StopTimer
    ActiveWorkbook.Save
    ActiveWorkbook.RefreshAll
    Range("F3:U3").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A2").Select
    Selection.Copy
    Range("F3").Select
    ActiveSheet.Paste
    Range("B4:B17").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("H3:U3").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveChart.PlotArea.Select
    Application.CutCopyMode = False
    ActiveChart.SetSourceData Source:=Range("F1:U1,F3:U51")
    Range("A1").Select
    StartTimer
End Sub
ERCOT-Price-Query.xlsm
0
 

Author Closing Comment

by:TravisBickle2
ID: 38454915
Thank you!
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Currently, there is an issue with being able to copy values from an external application to a dropdown list in Project Web Access (PWA).  The standard copy and paste methods don't seem to work properly. Here is a way to accomplish this task to s…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question