|
Servus,
Ich habe folgendes Problem ich will ein Funktion in eine Datei einfügen welche schon vorhanden ist. Es hat bis gestern funktioniert doch heute spricht er die Spalte A:A in der Datei Alt.xlsx nicht mehr an.
Hier mein gesamter Code:
UserForm1.Show vbModeless
Workbooks.Open Filename:="C:\Users\REGNERS\Desktop\Programmieren\Neu.xlsx"
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Windows("Einsatz453_Blanko1.xlsm").Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Dim ErsteFreie As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("G:G").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("G2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R1C[1]:RC[1],RC[1])"
Selection.AutoFill Destination:=Range("G2:G" & LastRow)
Range("AB2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-20],'C:\Users\REGNERS\Desktop\Programmieren\[alt.xlsx]Feuil1'!C9:C39,20,0)"
Selection.AutoFill Destination:=Range("AB2:AB" & LastRow)
Range("AC2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-21],'C:\Users\REGNERS\Desktop\Programmieren\[alt.xlsx]Feuil1'!C9:C39,21,0)"
Selection.AutoFill Destination:=Range("AC2:AC" & LastRow)
Range("AD2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-22],'C:\Users\REGNERS\Desktop\Programmieren\[alt.xlsx]Feuil1'!C9:C39,22,0)"
Selection.AutoFill Destination:=Range("AD2:AD" & LastRow)
Range("AE2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-23],'C:\Users\REGNERS\Desktop\Programmieren\[alt.xlsx]Feuil1'!C9:C39,23,0)"
Selection.AutoFill Destination:=Range("AE2:AE" & LastRow)
Range("AF2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-24],'C:\Users\REGNERS\Desktop\Programmieren\[alt.xlsx]Feuil1'!C9:C39,24,0)"
Selection.AutoFill Destination:=Range("AF2:AF" & LastRow)
Range("AG2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-25],'C:\Users\REGNERS\Desktop\Programmieren\[Start.xlsx]Startexport_2017-05-21_20-00-17'!C3:C17,15,0)"
Selection.AutoFill Destination:=Range("AG2:AG" & LastRow)
Range("AH2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-26],'C:\Users\REGNERS\Desktop\Programmieren\[alt.xlsx]Feuil1'!C9:C39,25,0)"
Selection.AutoFill Destination:=Range("AH2:AH" & LastRow)
Range("AI2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-27],'C:\Users\REGNERS\Desktop\Programmieren\[alt.xlsx]Feuil1'!C9:C39,26,0)"
Selection.AutoFill Destination:=Range("AI2:AI" & LastRow)
Range("AJ2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-28],'C:\Users\REGNERS\Desktop\Programmieren\[alt.xlsx]Feuil1'!C9:C39,27,0)"
Selection.AutoFill Destination:=Range("AJ2:AJ" & LastRow)
Range("AK2").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('C:\Users\REGNERS\Desktop\Programmieren\[Qlik.xlsx]Sheet1'!C13,RC[-29])"
Selection.AutoFill Destination:=Range("AK2:AK" & LastRow)
Range("AL2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-30],'C:\Users\REGNERS\Desktop\Programmieren\[alt.xlsx]Feuil1'!C9:C39,29,0)"
Selection.AutoFill Destination:=Range("AL2:AL" & LastRow)
Workbooks.Open Filename:="C:\Users\REGNERS\Desktop\Programmieren\alt.xlsx"
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Workbooks("alt.xlsx").Worksheets("Feuil1").Range("A1:A" & LastRow).Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF([Neu.xlsx]Feuil1!C1,RC[1])"
Selection.AutoFill Destination:=Range("A2:A" & LastRow)
Columns("A:A").Select
Selection.AutoFilter
Workbooks("alt.xlsx").Worksheets("Feuil1").Range("A1:A" & LastRow).AutoFilter Field:=1, Criteria1:="0", _
Operator:=xlAnd
Range("B2:AM" & LastRow).Select
Selection.Copy
Windows("Einsatz453_Blanko1.xlsm").Activate
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
ErsteFreie = LastRow + 1
Range("A" & ErsteFreie).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Cells.Select
Range("A2").Activate
Selection.Columns.AutoFit
Range("A1:AB1").Select
Selection.AutoFilter
With Selection.Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.349986266670736
.PatternTintAndShade = 0
End With
Range("AC1:AL1").Select
Selection.AutoFilter
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 15773696
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Columns("A:AL").Select
Selection.AutoFilter
Columns("B:B").Select
Columns("A:A").Select
Selection.RowHeight = 14.4
Columns("X:X").Select
Selection.NumberFormat = "dd-mmm-yy"
'Workbooks("alt.xlsx").Close SaveChanges:=False
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Unload UserForm1
Call naechste Funkiton
|