zu allererst moechte ich ein kurzes "Hallo" in die Runde werfen, ich bin neu hier im Forum und auch auf dem Gebiet VBA im Gegensatz zu dem geballten versammelten Fachwissen hier auch noch ein echtes Greenhorn :).
Ich habe ein groesseres Problem. Ich arbeite im Controlling fuer ein deutsches Unternehmen zur Zeit im Ausland und es geht mal wieder darauf zu das Budget fuer das naechste Geschaeftsjahr zu planen. Als Grundlage dafuer habe ich etliche SAP Kostenstellen Expense Reports, die ich nach Kostenstelle jeweils in einem Ordner abgespeichert habe. Noch einmal zur besseren Darstellung: Im Ordner "Budget" habe ich fuer die Kostenstellenreports Unterordner erstellt, z.B. fuer Kostenstelle "123" den Ordner "123" , folgend mit dem Ordner "456" fuer die Reports der Kostenstelle "456".
Im Ordner "Budget" ist ein "Masterfile" gespeichert, dass per klick auf einen Button auf dem ersten Tabellenblatt saemtliche vorhandene Kostenstellenreports in den jeweiligen Unterverzeichnissen (also im Ordner "Budget" alle Kostenstellenordner durchsucht) sucht und in eine Liste auf dem ersten Tabellenblatt ("Settings") in einer Liste ausliest und per Hyperlink direkt erreichbar macht. Die funktioniert prima.
Anbei dafuer folgender Code:
----------------------------------------------------------
Sub ListM()
Dim i As Long
Dim verz As String
verz = Range("pathnameact").Value
On Error Resume Next
ChDir verz
Range("b22").Select
With Application.FileSearch
.NewSearch
.LookIn = verz
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
.Execute
For i = 1 To .FoundFiles.Count
ActiveCell.Value = .FoundFiles(i)
ActiveCell.Offset(1, 0).Select
Next i
End With
Call Hyperlink
Exit Sub
Call ListFiles
End Sub
------------------------------------------------------------
Sub Hyperlink()
Dim Bereich As Range
Dim Zelle As Range
Range("b22").Select
Do Until ActiveCell.Value = ""
ActiveCell.Hyperlinks.Add ActiveCell, ActiveCell.Value
ActiveCell.Offset(1, 0).Select
Loop
End Sub
----------------------------------------------------------
Sub ListFiles()
Application.ScreenUpdating = False
Sheets("Data").Select
Range("C1:BA500").Select
Selection.ClearContents
Sheets("Settings").Select
End Sub
----------------------------------------------------------
Mit diesen 3 Codestuecken lese ich momentan die Kostenstellenreports aus den Unterverzeichnissen in eine Liste im Tabellenblatt "Settings" aus.
Folgend sollen die Inhalte der Kostenstellenreports in das Masterfile in nur ein Tabellenblatt kopiert werden, um von dort per Pivot analysiert zu werden. Da das Layout der Excel Exporte aus SAP eine direkte Kopierung und Verwendung des Inhalts unbrauchbar erscheinen laesst, kopiere ich den Inhalt der originalen Export-Reports selber zuerst ein neues Tabellenblatt im jeweiligen Datanblatt pro Kostenstelle wo die Daten in brauchbare Form gebraucht werden (es wird also in JEDEM Worksheet das einen Kostenstellenreport beinhaltet ein Tabellenblatt eingefuegt, auf dem die Daten in richtigem Format zum endgueltigen Kopieren ins Masterfile "vorbereitet werden).
Vorab zu sagen ist, dass es pro Kostenstelle pro Jahr 12 Reports gibt, also pro Periode/Monat einen. Jeder Ordner im Verzeichnis "Budget" enthaelt also am Geschaeftsjahresende 12 Reports. Da die Ueberpruefung der Ausgaben allerdings monatlich geschehen soll, muss jeden Monat diese Routine neu gestartet werden, um den jeweils aktuellen Datensatz ins Masterfile zu laden.
Anbei ein Codestueck mit dem ich die Daten aus den Kostenstellenreports erst wie oben angesprochen in den jeweiligen Workbooks zuerst ein neues Tabellenblatt "Rawdata" kopiere und von dortaus dann in das Masterfile kopiere/uebertrage:
----------------------------------------------------------
Sub datacopy()
Dim Mappe As String
Dim i As Integer
Dim strFile As String
Dim lastcolumn As Integer
Dim strPathNew As String
strPathNew = Range("pathnameact").Value
Application.ScreenUpdating = False
Mappe = ThisWorkbook.Name
Sheets("RawData").Select
Rows("3:3").Select
On Error Resume Next
With Application.FileSearch
.NewSearch
.LookIn = "strPathNew"
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
.Execute
For i = 2 To .FoundFiles.Count
Workbooks.Open .FoundFiles(i), UpdateLinks:=0
Sheets("True postings").Select 'True Postings beinhaltet den originalen SAP REPORT
Application.DisplayAlerts = False
ActiveWorkbook.Worksheets("RawData").Delete
Sheets.Add after:=Sheets("True postings")
ActiveSheet.Name = "RawData" 'erstellt neues Tabellenblatt in den Excel SAP Report _
_
_
Exports
Sheets("True postings").Range("B10").Copy
Sheets("RawData").Activate
lastrow = Sheets("True postings").UsedRange.Rows.Count
Range("C" & lastrow).End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False 'Kopiert die Kostenstellennummer, die statisch nur _
einmal pro Report auftaucht in das neue Tabellenblatt in Spalte C, so lange, wie Daten in _
Zeilen des Reports vorhanden sind
Sheets("True postings").Range("B6").Copy
Sheets("RawData").Activate
Range("D65536").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False 'Kopiert die jeweilige Periode, die statisch nur einmal _
_
_
pro Report auftaucht in das neue Tabellenblatt in Spalte D
Sheets("True postings").Range("B7").Copy
Sheets("RawData").Activate
Range("E65536").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False 'Kopiert das jeweilige Fiscal Year, die statisch nur _
_
_
einmal pro Report auftaucht in das neue Tabellenblatt in Spalte E
Sheets("True postings").Range("E13:E75").Copy
Sheets("RawData").Activate
Range("B65536").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("True postings").Range("B13:D75").Copy
Sheets("RawData").Activate
Range("F65536").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("True postings").Range("F13:K75").Copy
Sheets("RawData").Activate
Range("I65536").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
With Sheets("RawData")
.Range("C2:E2").Copy Destination:=.Range("C3:E" & .Range("F65536") _
.End(xlUp).Row)
End With
ActiveWorkbook.Save 'Kopiert in den uebertragenen Daten die Kostenstellennummer, _
Periode und Fiscal Year solange untereinander in die naechste Zeile, wie Daten im jeweiligen _
Report vorhanden sind.
Sheets("RawData").Activate
Range("B2:L75").Copy
Workbooks(Mappe).Activate
Sheets("RawData").Activate
Range("B65536").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Next i
Call AlleMappenBisAufDieAktiveSchließen
Sheets("Settings").Select
End With
Application.ScreenUpdating = True
End Sub
----------------------------------------------------------
Dies funktioniert auch soweit. Allerdings habe ich nun das Problem, dass alle Reports im Tabellenblatt "Rawdata" im Masterfile untereinander eingefuegt sind, was eine praktikable Auswertung der Daten fuer mich unmoeglich macht. Die Struktur der eingefuegten Reportdaten sieht Dabei folgendermassen aus:
Spalte C bis E werden dabei durch das VBA Makri so lange un die naechste Zeile kopiert, wie in Spalte F Zahlenwerte fuer die jeweilige Periode (1 bis 12) enthalten ist.
B (Cost Elements) C (Cost Center) D (Periode) E (Fiscal Year 2008) F ( Act. Period 1) G (BU Period 1) H (Variance Period 1) I (Act. YTD) J (BU YTD) K (Variance YTD)
Jetzt zu meiner Frage: Ich moechte die Daten Kostenstellenabhaengig im Tabellenblatt "umstellen". Bedeutet, dass fuer die Daten fuer jede Kostenstelle, die nun ALLE untereinander eingefuegt wurden, abhaengig nach Kostenstelle dementsprechend "umsortiert" werden, dass alle Reportdaten pro Kostenstelle nebeneinander stehen und pro Periode auch automatisch der naechste Monat/ die naechste Periode automatisch der entsprechenden Kostenstelle im Sheet zugefuegt wird.
Fuer Kostenstelle "123" moechte ich somit am Ende des Jahres alle Daten der 12 Perioden nebeneinander zum Beispiel von B1:N45 stehen habe. Folgend sollen die Daten fuer aller Perioden der Kostenstelle "456" im Bereich B46:N120 eingefuegt werden. Dies soll hierbei nur "bildlich" darstellen, wie ich mir den funktionalen Ablauf vorstelle. Leider kann man keine festen Arrays vergeben, da die bereits eingefuegten Reports aus SAP unterschiedlich viele Zeileneintraege besitzen, allerdings stets die selbe Spaltenanzahl und -bezeichnung.
Kann mann vielleicht eine Art Makro einfuegen als Schleife, die so lange die Werte in Spalte C und D vergleich und so lange die Daten ausschneidet und periodenweise nebeneinander einfuegt, so lange die Kostenstellennummer in Spalte C sich nicht aendert? Die Daten der darauf folgenden Kostenstelle muessten dann auch dementsprechend behandelt werden unter der Beruecksichtigung, dass der gesammte folgende Datensatz zudem auch noch im Tabellenblatt um die Anzahl der Zeilen der vorher ausgeschnittenen Dateninhalte der vorherigen Kostenstelle nach oben im Tabellenblatt nachruecken muss.
Das ist jetzt sehr viel Text, aber ich bin wirklich am verzweifeln. Es wuerde mich sehr freuen, wenn ihr euch meines Problems vielleicht annehmen koenntet! Vielen, vielen Dank!
Marcus