Hi all, The macro I have recorded to format sales data through power query won't run because I either get the error "Table 2 already exists" or "subscript out of range". Pretty new to VBA so I don't know how to debug it so I will post the code below and if anyone has any insight of whats wrong with it any help would be very appreciated. Thanks
VBA Code:
Sub AmazonSalesMacro()
'
' AmazonSalesMacro Macro
'
'
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Selection.Delete Shift:=xlUp
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Rows.AutoFit
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Rows.AutoFit
Range("B3").Select
Application.CutCopyMode = False
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$2:$BG$119"), , xlYes).Name = _
"Table2"
Range("Table2[#All]").Select
ActiveWorkbook.Queries.Add Name:="Table2", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""Table2""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source,{{""General"", type text}, {""Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type text}, {""Product"", type text}, {""Column5"", type text}, {""Column6"", type text}, {""Gross"", type any}, {""Col" & _
"umn7"", type any}, {""Column8"", type any}, {""Column9"", type any}, {""Column10"", type any}, {""Column11"", type any}, {""Column12"", type any}, {""Column13"", type any}, {""Column14"", type any}, {""Column15"", type any}, {""Promos"", type any}, {""Column16"", type any}, {""Column17"", type any}, {""Refunds"", type any}, {""Column18"", type any}, {""Column19"", t" & _
"ype any}, {""Column20"", type any}, {""Organic/PPC Orders"", type any}, {""Column21"", type any}, {""Column22"", type any}, {""FBA Fees"", type any}, {""Column23"", type any}, {""Column24"", type any}, {""Column25"", type any}, {""Column26"", type any}, {""Commission"", type any}, {""Costs"", type any}, {""Column27"", type any}, {""Column28"", type any}, {""Column29" & _
""", type any}, {""Column30"", type any}, {""Column31"", type any}, {""Net Profit"", type any}, {""Column32"", type any}, {""Column33"", type any}, {""Column34"", type any}, {""Column35"", type any}, {""Column36"", type any}, {""Column37"", type any}, {""Column38"", type any}, {""Column39"", type any}, {""Column40"", type any}, {""Stats"", type any}, {""Column41"", t" & _
"ype any}, {""Sessions"", type any}, {""Column42"", type any}, {""Column43"", type any}, {""Column44"", type any}, {""Column45"", type any}, {""Column46"", type any}, {""Column47"", type text}})," & Chr(13) & "" & Chr(10) & " #""Removed Columns"" = Table.RemoveColumns(#""Changed Type"",{""General""})," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(#""Removed Columns"", [PromoteAllScalars=" & _
"true])," & Chr(13) & "" & Chr(10) & " #""Changed Type1"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Date"", type date}, {""Group"", type text}, {""Name"", type text}, {""Merchant"", type text}, {""ASIN"", type text}, {""Parent ASIN"", type text}, {""SKU"", type text}, {""Total Sales"", type number}, {""Full Price Gross Sales"", type number}, {""Shipping"", type number}, {""Taxes" & _
""", type number}, {""Amz Withheld Taxes"", type number}, {""Gift Wrap"", Int64.Type}, {""Reimbursements"", type number}, {""Total Orders"", Int64.Type}, {""Total Units"", Int64.Type}, {""Full Price Units"", Int64.Type}, {""Promo Sales"", type number}, {""Discounts"", Int64.Type}, {""Promo Units"", Int64.Type}, {""Refunds"", Int64.Type}, {""Refund Sales"", type numbe" & _
"r}, {""Refund Fees"", type number}, {""Net Refund"", type number}, {""Organic Orders"", Int64.Type}, {""Same SKU PPC Orders"", Int64.Type}, {""All SKUs PPC Orders"", Int64.Type}, {""Pick & Pack"", type number}, {""Handling Fee"", Int64.Type}, {""Weight Handling"", Int64.Type}, {""Variable Cost"", Int64.Type}, {""Total FBA Fees"", type number}, {""Refer Fees"", type " & _
"number}, {""Product Cost"", type number}, {""Shipping Cost"", type number}, {""Return Credit"", type number}, {""Ads"", type number}, {""Tax"", type number}, {""Total Costs"", type number}, {""Total Gross Sales"", type number}, {""Amz FBM Postage"", type number}, {""3rd Party Postage"", Int64.Type}, {""Promo Discounts"", Int64.Type}, {""FBA Fees"", type number}, {""" & _
"Other Transactions"", type number}, {""Commissions"", type number}, {""Costs"", type number}, {""Refund Total"", type number}, {""Net Profit"", type number}, {""ROI"", type number}, {""Margin"", type number}, {""Sessions"", Int64.Type}, {""Session %"", Int64.Type}, {""Page Views"", Int64.Type}, {""Page Views %"", Int64.Type}, {""Buy Box"", Int64.Type}, {""Unit Sess " & _
"%"", Int64.Type}, {""Currency"", type text}})," & Chr(13) & "" & Chr(10) & " #""Renamed Columns"" = Table.RenameColumns(#""Changed Type1"",{{""Name"", ""Product Title""}, {""Merchant"", ""Channel""}, {""Group"", ""Product Sub Type""}})," & Chr(13) & "" & Chr(10) & " #""Removed Columns1"" = Table.RemoveColumns(#""Renamed Columns"",{""Parent ASIN""})," & Chr(13) & "" & Chr(10) & " #""Renamed Columns1"" = Table.RenameColumns(#""Removed Column" & _
"s1"",{{""SKU"", ""Variant SKU""}})," & Chr(13) & "" & Chr(10) & " #""Removed Columns2"" = Table.RemoveColumns(#""Renamed Columns1"",{""Session %"", ""Page Views"", ""Page Views %"", ""Buy Box"", ""Unit Sess %"", ""Currency"", ""Sessions"", ""Margin"", ""ROI"", ""Net Profit"", ""Refund Total"", ""Costs"", ""Commissions"", ""Other Transactions"", ""FBA Fees"", ""Promo Discounts"", ""3rd Party" & _
" Postage"", ""Amz FBM Postage"", ""Total Costs"", ""Ads"", ""Tax"", ""Return Credit"", ""Shipping Cost"", ""Refer Fees"", ""Handling Fee"", ""Weight Handling"", ""Variable Cost"", ""Pick & Pack"", ""Same SKU PPC Orders"", ""All SKUs PPC Orders"", ""Organic Orders"", ""Refund Fees"", ""Net Refund"", ""Total FBA Fees"", ""Discounts"", ""Promo Units"", ""Refunds"", ""R" & _
"efund Sales"", ""Product Cost"", ""Promo Sales"", ""Full Price Units"", ""Reimbursements"", ""Amz Withheld Taxes"", ""Gift Wrap"", ""Taxes"", ""Shipping"", ""Full Price Gross Sales""})," & Chr(13) & "" & Chr(10) & " #""Reordered Columns"" = Table.ReorderColumns(#""Removed Columns2"",{""Product Title"", ""Date"", ""Product Sub Type"", ""Channel"", ""ASIN"", ""Variant SKU"", ""Total Sales"", " & _
"""Total Orders"", ""Total Units"", ""Total Gross Sales""})," & Chr(13) & "" & Chr(10) & " #""Added Custom"" = Table.AddColumn(#""Reordered Columns"", ""Product Vendor"", each ""Rukket Sports"")," & Chr(13) & "" & Chr(10) & " #""Reordered Columns1"" = Table.ReorderColumns(#""Added Custom"",{""Product Title"", ""Product Vendor"", ""Date"", ""Product Sub Type"", ""Channel"", ""ASIN"", ""Variant SKU"", ""Total Sales""," & _
" ""Total Orders"", ""Total Units"", ""Total Gross Sales""})," & Chr(13) & "" & Chr(10) & " #""Split Column by Delimiter"" = Table.SplitColumn(#""Reordered Columns1"", ""Product Sub Type"", Splitter.SplitTextByDelimiter(""-"", QuoteStyle.Csv), {""Product Sub Type.1"", ""Product Sub Type.2"", ""Product Sub Type.3""})," & Chr(13) & "" & Chr(10) & " #""Changed Type2"" = Table.TransformColumnTypes(#""Split Column by Del" & _
"imiter"",{{""Product Sub Type.1"", type text}, {""Product Sub Type.2"", type text}, {""Product Sub Type.3"", type text}})," & Chr(13) & "" & Chr(10) & " #""Reordered Columns2"" = Table.ReorderColumns(#""Changed Type2"",{""Product Title"", ""Product Vendor"", ""Product Sub Type.1"", ""Date"", ""Product Sub Type.2"", ""Product Sub Type.3"", ""Channel"", ""ASIN"", ""Variant SKU"", ""Total Sale" & _
"s"", ""Total Orders"", ""Total Units"", ""Total Gross Sales""})," & Chr(13) & "" & Chr(10) & " #""Renamed Columns2"" = Table.RenameColumns(#""Reordered Columns2"",{{""Product Sub Type.1"", ""Product Type""}})," & Chr(13) & "" & Chr(10) & " #""Added Custom1"" = Table.AddColumn(#""Renamed Columns2"", ""Product Price"", each """")," & Chr(13) & "" & Chr(10) & " #""Reordered Columns3"" = Table.ReorderColumns(#""Added Custom1"",{""Product Title" & _
""", ""Product Vendor"", ""Product Type"", ""Product Price"", ""Variant SKU"", ""Date"", ""Product Sub Type.2"", ""Product Sub Type.3"", ""Channel"", ""ASIN"", ""Total Sales"", ""Total Orders"", ""Total Units"", ""Total Gross Sales""})," & Chr(13) & "" & Chr(10) & " #""Renamed Columns3"" = Table.RenameColumns(#""Reordered Columns3"",{{""Total Units"", ""Net Quantity""}})," & Chr(13) & "" & Chr(10) & " #""Reordered C" & _
"olumns4"" = Table.ReorderColumns(#""Renamed Columns3"",{""Product Title"", ""Product Vendor"", ""Product Type"", ""Product Price"", ""Variant SKU"", ""Net Quantity"", ""Total Gross Sales"", ""Total Sales"", ""Date"", ""Product Sub Type.2"", ""Product Sub Type.3"", ""Channel"", ""ASIN"", ""Total Orders""})," & Chr(13) & "" & Chr(10) & " #""Added Custom2"" = Table.AddColumn(#""Reordered Colum" & _
"ns4"", ""Returns"", each """")," & Chr(13) & "" & Chr(10) & " #""Added Custom3"" = Table.AddColumn(#""Added Custom2"", ""Discounts"", each """")," & Chr(13) & "" & Chr(10) & " #""Reordered Columns5"" = Table.ReorderColumns(#""Added Custom3"",{""Product Title"", ""Product Vendor"", ""Product Type"", ""Product Price"", ""Variant SKU"", ""Net Quantity"", ""Total Gross Sales"", ""Discounts"", ""Returns"", ""Total Sales" & _
""", ""Date"", ""Product Sub Type.2"", ""Product Sub Type.3"", ""Channel"", ""ASIN"", ""Total Orders""})," & Chr(13) & "" & Chr(10) & " #""Renamed Columns4"" = Table.RenameColumns(#""Reordered Columns5"",{{""Total Sales"", ""Net Sales""}})," & Chr(13) & "" & Chr(10) & " #""Added Custom4"" = Table.AddColumn(#""Renamed Columns4"", ""Taxes"", each """")," & Chr(13) & "" & Chr(10) & " #""Reordered Columns6"" = Table.ReorderColumns(#""Added Cust" & _
"om4"",{""Product Title"", ""Product Vendor"", ""Product Type"", ""Product Price"", ""Variant SKU"", ""Net Quantity"", ""Total Gross Sales"", ""Discounts"", ""Returns"", ""Net Sales"", ""Taxes"", ""Date"", ""Product Sub Type.2"", ""Product Sub Type.3"", ""Channel"", ""ASIN"", ""Total Orders""})," & Chr(13) & "" & Chr(10) & " #""Added Custom5"" = Table.AddColumn(#""Reordered Columns6"", ""Net" & _
""
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Table2;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Table2]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table2_2"
.Refresh BackgroundQuery:=False
End With
Application.CommandBars("Queries and Connections").Visible = False
Range("A6").Select
End Sub
