benjigarc123
New Member
- Joined
- Aug 25, 2017
- Messages
- 1
Hello, I have this file 08.22.2017_13.07.34.csv whose name changes daily with respective date and time. Using the macro below i am only able to use it with this file name. What I want it to do is be able to use it with any excel file that i open. I also want it to save using the current workbook's name. Please see macro below:
Sub Macro5()
'
' Macro5 Macro
'
' Keyboard Shortcut: Ctrl+e
'
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Windows("MASTER Customer Import.xlsx").Activate
Range("A1").Select
Selection.Copy
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("08.22.2017_13.07.34.csv").Activate
Range("A1").Select
ActiveSheet.Paste
Windows("MASTER Customer Import.xlsx").Activate
Range("A2:B2").Select
Application.CutCopyMode = False
Selection.Copy
Windows("08.22.2017_13.07.34.csv").Activate
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("A2:B102")
Range("A2:B102").Select
Columns("J:J").Select
Selection.Copy
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
Columns("AD:AD").Select
ActiveSheet.Paste
Range("AD1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "VendorSku1"
Columns("Q:Q").Select
Selection.NumberFormat = "###-###-####"
Columns("P:P").Select
Selection.NumberFormat = "00000"
ChDir "F:\PCRX Daily Batches"
ActiveWorkbook.SaveAs Filename:= _
"F:\PCRX Daily Batches\08.22.2017_13.07.34.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ChDir "F:\Graphics Folder\PetCare RX Orders"
ActiveWorkbook.SaveAs Filename:= _
"F:\Graphics Folder\PetCare RX Orders\08.22.2017_13.07.34.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.SaveAs Filename:= _
"F:\Graphics Folder\PetCare RX Orders\CustomerImport.txt", FileFormat:=xlText _
, CreateBackup:=False
End Sub
Sub Macro5()
'
' Macro5 Macro
'
' Keyboard Shortcut: Ctrl+e
'
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Windows("MASTER Customer Import.xlsx").Activate
Range("A1").Select
Selection.Copy
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("08.22.2017_13.07.34.csv").Activate
Range("A1").Select
ActiveSheet.Paste
Windows("MASTER Customer Import.xlsx").Activate
Range("A2:B2").Select
Application.CutCopyMode = False
Selection.Copy
Windows("08.22.2017_13.07.34.csv").Activate
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("A2:B102")
Range("A2:B102").Select
Columns("J:J").Select
Selection.Copy
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
Columns("AD:AD").Select
ActiveSheet.Paste
Range("AD1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "VendorSku1"
Columns("Q:Q").Select
Selection.NumberFormat = "###-###-####"
Columns("P:P").Select
Selection.NumberFormat = "00000"
ChDir "F:\PCRX Daily Batches"
ActiveWorkbook.SaveAs Filename:= _
"F:\PCRX Daily Batches\08.22.2017_13.07.34.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ChDir "F:\Graphics Folder\PetCare RX Orders"
ActiveWorkbook.SaveAs Filename:= _
"F:\Graphics Folder\PetCare RX Orders\08.22.2017_13.07.34.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.SaveAs Filename:= _
"F:\Graphics Folder\PetCare RX Orders\CustomerImport.txt", FileFormat:=xlText _
, CreateBackup:=False
End Sub