Extract data a URL and create pivot table

[thomasgibson]

Hi guys,

New to excel macros, but having seen a couple of vids on You Tube I'm quickly becoming fascinated by its potential!

So anyway, there's a task a run every morning and I think a macro could help but i'm hitting huddles. Wonder if anyone can help?

The task

1 - Take 2 csv downloads (around 750 rows and 93 columns) and combine in a new tab of a document from a URL address.
2 - Create another tab and create a pivot table within the tab using column T for the pivot table row and column E for the pivot table columns.

I've tried to do this with a macro but with little success.

So far I've made a Apple Automator to download the files from the URL and the following macro:

Sub CreatePivotTable()
'PURPOSE: Creates a brand new Pivot table on a new worksheet from data in the ActiveSheet
'Source: www.TheSpreadsheetGuru.com


Dim sht As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As String
Dim SrcData As String


'Determine the data range you want to pivot
SrcData = ActiveSheet.Name & "!" & Range("A1:R100").Address(ReferenceStyle:=xlR1C1)


'Create a new worksheet
Set sht = Sheets.Add


'Where do you want Pivot Table to start?
StartPvt = sht.Name & "!" & sht.Range("A3").Address(ReferenceStyle:=xlR1C1)


'Create Pivot Cache from Source Data
Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=SrcData)


'Create Pivot table from Pivot Cache
Set pvt = pvtCache.CreatePivotTable( _
TableDestination:=StartPvt, _
TableName:="PivotTable1")

'Add item to the Row Labels
pvt.PivotFields("Status").Orientation = xlRowField


End Sub


I'm trying to find macros online, but struggling to stitch them together.

Can anyone help a macro newbie out?

Thanks!

 

[thomasgibson]

***So far I've made a Apple Automator to download the files from the URL, and tried to use the following macro: