tonywatsonhelp
Well-known Member
- Joined
- Feb 24, 2014
- Messages
- 3,194
- Office Version
- 365
- 2019
- 2016
- Platform
- Windows
Hi Everyone,
I have the code below which basically opens a document and pastes data in then closes it.
it works great but I want to rename the document it opens then save it as the new document so the document I open remains unchanged.
Fpath is the file path and where id like the document saved.
fName is the master document we are opening
NewName is the name i'd like to call the document (i.e. Tonys Doc1.xlsm)
the other thing I need to do is check if that documents name already exists in the folder and if so if its possible move it to a folder called "old versions" which is in the folder Fpath
i'm not sure how to do this?
please help if you can
thanks
Tony
I have the code below which basically opens a document and pastes data in then closes it.
it works great but I want to rename the document it opens then save it as the new document so the document I open remains unchanged.
Fpath is the file path and where id like the document saved.
fName is the master document we are opening
NewName is the name i'd like to call the document (i.e. Tonys Doc1.xlsm)
the other thing I need to do is check if that documents name already exists in the folder and if so if its possible move it to a folder called "old versions" which is in the folder Fpath
VBA Code:
Sub Export()
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Fpath = Worksheets("Overview").Range("AR60")
fName = Worksheets("Overview").Range("AR64")
NewName = Worksheets("Overview").Range("AR66")
Workbooks.Open Fpath
ThisWorkbook.Activate
Sheets("ant").Range(Sheets("ant").Range("I3")).SpecialCells(xlCellTypeVisible).Copy
Workbooks(fName).Activate
Lrowp = Sheets("Dates").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Dates").Range("A" & Lrowp).PasteSpecial Paste:=xlPasteValues
ThisWorkbook.Activate
Application.DisplayAlerts = False
Workbooks(fName).Close SaveChanges:=True
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlAutomatic
End Sub
please help if you can
thanks
Tony