Hi Leute,

ich bekomme bei meinem aufgezeichneten Makro die Fehlermeldung "Laufzeitfehler 9"... Wenn ich auf Debuggen gehe ist dann "Sheets("Heute").Select" gelb markiert. Manchmal ist auch "Sheets("Heute (3)").Name = "Heute"" gelb markiert. Ich habe das Makro aufgezeichnet und "Heute" ist ein Dateiname und der Name von dem Arbeitsblatt dadrin. "Heute (3)" estand beim aufzeichnen im Power Query. Kann jemand mir dabei helfen, diese Fehlermeldung zu beseitigen?
Und meine zweite Frage wäre: Kann man mit Excel Makros überhaupt so ein "komplexes" Vorhaben wie meins umsetzen? 
Ich möchte mit einer "Excelarbeitsmappe mit Makro", mit einem Makro, mehrere Dateien/Tabellen abrufen, erst 2 Tabellen davon über den Power Query zusammeführen, dann innerhalb von anderen Blättern arbeiten und von diesen Zeilen in andere Blätter kopieren, dann wieder bereits zusammengeführte Tabellen, wieder mit einer weiteren Tabelle zusammenführen (Power Query). Dies ganze möchte ich in einer Schleife laufen lassen, in der sich der Zugriff auf die jeweiligen Datei (Daten Abrufen) nach dem Counter verändert; bspw. im ersten Durchgang gibt er den Pfad "Workbooks.Open "C:\Users\1992j\OneDrive\Desktop\1-" & Format(Now, "dd.mm.yyyy") & ".xlsx""; beim zweiten Durchgang kommt statt der 1 eine 2, beim dritten dann eine 3 und so weiter.

Ich habe mich erst seit ca. 3 Tagen mit Excel VBA beschäftigt und bin Amateur.

Ich danke euch im voraus. 

Gruß Bernd
 

Sub Shop_Makro()
'
' Shop_Makro Makro
'

'
    ActiveWorkbook.Queries.Add Name:="Heute (3)", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Quelle = Excel.Workbook(File.Contents(""C:\Users\1992j\OneDrive\Desktop\Test VBAAAA\Heute.xlsx""), null, true)," & Chr(13) & "" & Chr(10) & "    Heute_Sheet = Quelle{[Item=""Heute"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Höher gestufte Header"" = Table.PromoteHeaders(Heute_Sheet, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Geänderter Typ"" = Table.TransformColumnTypes(#""Höher gestufte Header"",{{""A" & _
        "SIN"", type text}, {""Verkäufe"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Geänderter Typ"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Heute (3)"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Heute (3)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Heute__3"
        .Refresh BackgroundQuery:=False
    End With
    Sheets("Heute").Select
    Sheets("Heute (3)").Name = "Heute"
    Range("Heute__3[#All]").Select
    ActiveWorkbook.Queries.Add Name:="Heute__3", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Quelle = Excel.CurrentWorkbook(){[Name=""Heute__3""]}[Content]," & Chr(13) & "" & Chr(10) & "    #""Geänderter Typ"" = Table.TransformColumnTypes(Quelle,{{""ASIN"", type text}, {""Verkäufe"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Geänderter Typ"""
    ActiveWorkbook.Queries.Add Name:="Gestern", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Quelle = Excel.Workbook(File.Contents(""C:\Users\1992j\OneDrive\Desktop\Test VBAAAA\Gestern.xlsx""), null, true)," & Chr(13) & "" & Chr(10) & "    Gestern_Sheet = Quelle{[Item=""Gestern"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Höher gestufte Header"" = Table.PromoteHeaders(Gestern_Sheet, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Geänderter Typ"" = Table.TransformColumnTypes(#""Höher gestufte Header" & _
        """,{{""ASIN"", type text}, {""Verkäufe"", Int64.Type}})," & Chr(13) & "" & Chr(10) & "    #""Zusammengeführte Abfragen"" = Table.NestedJoin(#""Geänderter Typ"", {""ASIN""}, #""Heute (3)"", {""ASIN""}, ""Heute (3)"", JoinKind.LeftOuter)," & Chr(13) & "" & Chr(10) & "    #""Erweiterte Heute (3)"" = Table.ExpandTableColumn(#""Zusammengeführte Abfragen"", ""Heute (3)"", {""ASIN"", ""Verkäufe""}, {""Heute (3).ASIN"", ""Heute " & _
        "(3).Verkäufe""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Erweiterte Heute (3)"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Heute__3;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Heute__3]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Heute__4"
        .Refresh BackgroundQuery:=False
    End With
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Gestern;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Gestern]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Gestern"
        .Refresh BackgroundQuery:=False
    End With
    Sheets("Gestern").Select
    ActiveWorkbook.Queries.Add Name:="Rang", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Quelle = Excel.Workbook(File.Contents(""C:\Users\1992j\OneDrive\Desktop\Test VBAAAA\Rang.xlsx""), null, true)," & Chr(13) & "" & Chr(10) & "    Rang_Sheet = Quelle{[Item=""Rang"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Höher gestufte Header"" = Table.PromoteHeaders(Rang_Sheet, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Geänderter Typ"" = Table.TransformColumnTypes(#""Höher gestufte Header"",{{""ASIN" & _
        """, type text}, {""Rang Gestern"", Int64.Type}, {""Impressionen Gestern"", Int64.Type}, {""Faktor"", type number}, {""Methode Gestern:"", type any}, {""Column6"", type any}, {""Impressionen Gesamt Gestern:"", type any}, {""Column8"", Int64.Type}, {""Verkäufe Gestern:"", type any}, {""Column10"", type any}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Geänderter Typ"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Rang;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Rang]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Rang"
        .Refresh BackgroundQuery:=False
    End With
    ActiveWorkbook.Queries.Add Name:="Werbedaten", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Quelle = Excel.Workbook(File.Contents(""C:\Users\1992j\OneDrive\Desktop\Test VBAAAA\Werbedaten.xlsx""), null, true)," & Chr(13) & "" & Chr(10) & "    Werbedaten_Sheet = Quelle{[Item=""Werbedaten"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Höher gestufte Header"" = Table.PromoteHeaders(Werbedaten_Sheet, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Geänderter Typ"" = Table.TransformColumnTypes(#""Höher ges" & _
        "tufte Header"",{{""Product"", type text}, {""Entity"", type text}, {""Operation"", type text}, {""Campaign Id"", Int64.Type}, {""Draft Campaign Id"", type text}, {""Portfolio Id"", Int64.Type}, {""Ad Group Id (Read only)"", Int64.Type}, {""Keyword Id (Read only)"", Int64.Type}, {""Product Targeting Id (Read only)"", type text}, {""Campaign Name"", type text}, {""Cam" & _
        "paign Name (Informational only)"", type text}, {""Portfolio Name (Informational only)"", type text}, {""Start Date"", Int64.Type}, {""End Date"", type text}, {""State"", type text}, {""Campaign State (Informational only)"", type text}, {""Campaign Serving Status (Informational only)"", type text}, {""Budget Type"", type text}, {""Budget"", Int64.Type}, {""Bid Optimi" & _
        "zation"", type text}, {""Bid Multiplier"", Percentage.Type}, {""Bid"", type number}, {""Keyword Text"", type text}, {""Match Type"", type text}, {""Product Targeting Expression"", type text}, {""Resolved Product Targeting Expression (Informational only)"", type text}, {""Ad Format"", type text}, {""Ad Format (Informational only)"", type text}, {""Landing Page URL""," & _
        " type text}, {""Landing Page Asins"", type text}, {""Landing Page Type (Informational only)"", type text}, {""Brand Entity Id"", type text}, {""Brand Name"", type text}, {""Brand Logo Asset Id"", type text}, {""Brand Logo URL"", type text}, {""Creative Headline"", type text}, {""Creative ASINs"", type text}, {""Video Media Ids"", type text}, {""Creative Type"", type" & _
        " text}, {""Impressions"", Int64.Type}, {""Verkäufe"", Int64.Type}, {""Click-through Rate"", type number}, {""Spend"", type number}, {""Sales"", Int64.Type}, {""Orders"", Int64.Type}, {""Units"", Int64.Type}, {""Conversion Rate"", Int64.Type}, {""Acos"", Int64.Type}, {""CPC"", type number}, {""ROAS"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Geänderter Typ"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Werbedaten;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Werbedaten]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Werbedaten"
        .Refresh BackgroundQuery:=False
    End With
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 23
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 25
    ActiveWindow.ScrollColumn = 27
    ActiveWindow.ScrollColumn = 28
    ActiveWindow.ScrollColumn = 29
    ActiveWindow.ScrollColumn = 30
    ActiveWindow.ScrollColumn = 31
    ActiveWindow.ScrollColumn = 32
    ActiveWindow.ScrollColumn = 33
    ActiveWindow.ScrollColumn = 34
    ActiveWindow.ScrollColumn = 35
    ActiveWindow.ScrollColumn = 36
    ActiveWindow.ScrollColumn = 37
    ActiveWindow.ScrollColumn = 38
    Range("AN2").Select
    Selection.Copy
    Sheets("Rang").Select
    Range("Rang[[#Headers],[Faktor]]").Select
    ActiveSheet.Paste
    Range("E23").Select
    Sheets("Werbedaten").Select
    Application.CutCopyMode = False
    Range("AO2").Select
    Selection.Copy
    Sheets("Rang").Select
    Range("Rang[[#Headers],[Column8]]").Select
    ActiveSheet.Paste
    Range("Rang[[#Headers],[4]]").Select
    Application.CutCopyMode = False
    Selection.Style = "Akzent2"
    Range("Rang[[#Headers],[650]]").Select
    Selection.Style = "Akzent2"
    Range("Rang").Select
    Range("J2").Activate
    Selection.Copy
    Sheets("Gestern").Select
    Range("F1").Select
    ActiveSheet.Paste
    Range("G20").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    ActiveWindow.SmallScroll Down:=-15
    Sheets("Rang").Select
    Range("Rang[#All]").Select
    Range("J4").Activate
    Selection.Copy
    Sheets("Gestern").Select
    Range("F1").Select
    ActiveSheet.Paste
    Range("G11").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    ActiveWorkbook.Save
    ActiveWorkbook.Save
End Sub