AW: VBA Dateien in neues Dokument übertragen
07.04.2020 11:38:40
Nepumuk
Hallo Schlandro,
teste mal:
Option Explicit
Public Sub Filtern()
Const FOLDER_PATH As String = "C:\Users\simon\Desktop\Excel-Test\"
Dim objWorkbook As Workbook
Call Rows(1).AutoFilter(Field:=1, Criteria1:="AL")
Call ActiveSheet.AutoFilter.Range.Copy
Set objWorkbook = Workbooks.Add(Template:=xlWBATWorksheet)
With objWorkbook.Worksheets(1)
Call .Paste(Destination:=.Cells(1, 1))
End With
Call objWorkbook.SaveAs(Filename:=FOLDER_PATH & "AL", FileFormat:=xlOpenXMLWorkbook)
Call objWorkbook.ExportAsFixedFormat(Type:=xlTypePDF, Filename:=FOLDER_PATH & "AL", _
Quality:=xlQualityStandard, OpenAfterPublish:=False)
Call objWorkbook.Close(SaveChanges:=False)
Call Rows(1).AutoFilter(Field:=1, Criteria1:="PL")
Call ActiveSheet.AutoFilter.Range.Copy
Set objWorkbook = Workbooks.Add(Template:=xlWBATWorksheet)
With objWorkbook.Worksheets(1)
Call .Paste(Destination:=.Cells(1, 1))
End With
Call objWorkbook.SaveAs(Filename:=FOLDER_PATH & "PL", FileFormat:=xlOpenXMLWorkbook)
Call objWorkbook.ExportAsFixedFormat(Type:=xlTypePDF, Filename:=FOLDER_PATH & "PL", _
Quality:=xlQualityStandard, OpenAfterPublish:=False)
Call objWorkbook.Close(SaveChanges:=False)
Call Rows(1).AutoFilter(Field:=1, Criteria1:="PP")
Call ActiveSheet.AutoFilter.Range.Copy
Set objWorkbook = Workbooks.Add(Template:=xlWBATWorksheet)
With objWorkbook.Worksheets(1)
Call .Paste(Destination:=.Cells(1, 1))
End With
Call objWorkbook.SaveAs(Filename:=FOLDER_PATH & "PP", FileFormat:=xlOpenXMLWorkbook)
Call objWorkbook.ExportAsFixedFormat(Type:=xlTypePDF, Filename:=FOLDER_PATH & "PP", _
Quality:=xlQualityStandard, OpenAfterPublish:=False)
Call objWorkbook.Close(SaveChanges:=False)
Call Rows(1).AutoFilter(Field:=1, Criteria1:="PuL")
Call ActiveSheet.AutoFilter.Range.Copy
Set objWorkbook = Workbooks.Add(Template:=xlWBATWorksheet)
With objWorkbook.Worksheets(1)
Call .Paste(Destination:=.Cells(1, 1))
End With
Call objWorkbook.SaveAs(Filename:=FOLDER_PATH & "PuL", FileFormat:=xlOpenXMLWorkbook)
Call objWorkbook.ExportAsFixedFormat(Type:=xlTypePDF, Filename:=FOLDER_PATH & "PuL", _
Quality:=xlQualityStandard, OpenAfterPublish:=False)
Call objWorkbook.Close(SaveChanges:=False)
Call Rows(1).AutoFilter(Field:=1, Criteria1:="QS")
Call ActiveSheet.AutoFilter.Range.Copy
Set objWorkbook = Workbooks.Add(Template:=xlWBATWorksheet)
With objWorkbook.Worksheets(1)
Call .Paste(Destination:=.Cells(1, 1))
End With
Call objWorkbook.SaveAs(Filename:=FOLDER_PATH & "QS", FileFormat:=xlOpenXMLWorkbook)
Call objWorkbook.ExportAsFixedFormat(Type:=xlTypePDF, Filename:=FOLDER_PATH & "QS", _
Quality:=xlQualityStandard, OpenAfterPublish:=False)
Call objWorkbook.Close(SaveChanges:=False)
End Sub
Gruß
Nepumuk