I have a file where some VBA is ran which creates an output into a new file and does some formatting etc and then the VBA return to the original file where the VBa was first ran from. The issue is the user will sometimes change the file name which creates an error in my code.
Please see my code below with the highlighted file name which will change.
The problem is with the reference to (ORIGINAL FILE) as the user will rename the file.
Please see my code below with the highlighted file name which will change.
The problem is with the reference to (ORIGINAL FILE) as the user will rename the file.
Code:
Sub CreateOrder()
'
' CreateOrder Macro
'
'
Response = Application.InputBox("Enter Password to execute", "Password Required")
If Not Response = "lee" Then Exit Sub
Application.ScreenUpdating = False
Sheets("Order Create").Visible = xlSheetVisible
Sheets("Order Create").Select
Columns("A:AT").Select
Selection.Delete Shift:=xlToLeft
Sheets("ORIGINAL SHEET").Select
Range("BC23").Select
Selection.AutoFilter
Selection.AutoFilter
ActiveSheet.Range("$BB$23:$BC$4290").AutoFilter Field:=2, Criteria1:= _
"ORDERED"
Range("A22:AY4290").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Order Create").Visible = True
Sheets("Order Create").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C:C,E:E,F:F,G:G,I:I,K:K").Select
Range("K1").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
Call PATOrder_Click
Windows("ORIGINAL FILE.xlsm").Activate
Sheets("ORIGINAL SHEET").Select
Selection.AutoFilter
Range("A21").Select
Application.ScreenUpdating = True
MsgBox "PAT Order Created Successfully"
End Sub
Sub PATOrder_Click()
Dim Rng As Range, Dn As Range, RngAc As Range
Dim Lst As Long, Ac As Long, Ray() As Variant, c As Long
Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
c = 1
For Each Dn In Rng
Lst = Cells(Dn.Row, Columns.Count).End(xlToLeft).Column
If Lst > 4 Then
For Ac = 5 To Lst
If Dn.Offset(, Ac).Value <> "" Then
c = c + 1
ReDim Preserve Ray(1 To 4, 1 To c)
Ray(1, c) = Dn.Value
Ray(2, c) = Dn.Offset(, 2)
Ray(3, c) = Dn.Offset(, Ac).Value * Dn.Offset(, 2).Value
Ray(4, c) = Cells(1, Ac + 1)
End If
Next Ac
End If
Next Dn
Ray(1, 1) = "Variety": Ray(2, 1) = "Sales PF": Ray(3, 1) = "Amount": Ray(4, 1) = "For week"
Sheets("Order Create").Visible = xlSheetVeryHidden
Workbooks.Add
ActiveWorkbook.Windows(1).Caption = "PAT_IMPORT"
With Sheets("Sheet1").Range("A1").Resize(c, 4)
.Value = Application.Transpose(Ray)
.Borders.Weight = 2
.Columns.AutoFit
Range("E1").Select
ActiveCell.FormulaR1C1 = "Year"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",IF(RC[-1]=48,2017,2018))"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E15000")
Range("E2:E15000").Select
Columns("E:E").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("F1").Select
Columns("A:E").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Windows("ORIGINAL FILE.xlsm").Activate
Sheets("PAT_ORDER").Visible = xlSheetVisible
Sheets("PAT_ORDER").Select
Range("A1:AF1").Select
Selection.Copy
Windows("PAT_IMPORT").Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
Windows("ORIGINAL FILE.xlsm").Activate
Sheets("PAT_ORDER").Visible = xlSheetVeryHidden
End With
End Sub
