Macro to open a master document copy data into it save it as a new document

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
3,194
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. 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

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
i'm not sure how to do this?
please help if you can
thanks
Tony
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Anyone have any ideas on this it would be very helpful
Thank you
Tony
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top