Macro to save file in different location

Hi Sam, could you be more specific? I don't quite understand what you mean by "or the cell contents of one cell in the file in it's current location".

Barrie

Hi Barrie, What I'm trying to do is save the opened file to a different location than from where I opened it from. I have a macro that installs an assending number on the spreadsheet when the file opens. I also use another macro to save the file to a different location including the new number as part of the
file name. What I want to do is some how copy this new number to the file in the first location where the orginal file is stored so the next time the file is opened again the number will be one higher than the previous opening. Hope this makes sense.
Thanks, Sam

Hi again Sam. If I understand what you are currently doing.......
You save a file that you already have open (say Book 1) using a numbering system that attaches a number to the SaveAs file name. You want to be able to paste that number to a cell within a different file (say Book 2, sheet 1, cell A1). Is this correct? Is "Book 2" open when you are running your SaveAs macro? Does your SaveAs macro get its number from "Book 2"

Barrie

Hi Barrie, Thanks for all your trouble so far. Actually book 1 is the book that I'm saving to a different location. I would also like to keep book 1 in it's orginal location but with the new number pasted in so that the next time it is opened it will be one number higher than the same book saved to the different location. I would also like to keep book 1 empty of all data in it's orginal location, except for the new number. Is there any way that I can paste a number into that file without getting a pop up window asking me if I want to replace the current file. Maybe I can explain this a little better. Let's say I have a file named work orders in C:\my files. I open this file. when I do I have a macro that increases a number in one cell of the workbook by one
when the workbook opens. When I'm finished entering data to this workbook, I have another macro attached to a button on the form that includes that number as the last part of the file name and saves the file to a different location, let's say C:\archives. What I want to accomplish is, the new number needs to be transfered back to the workbook in it's orginal location so that the next time the workbook is opened the number will be one higher than the number on the workbook that was saved to the different
location. This will prevent overwritting the workbook in the other location when the workbook is saved again.

Thanks, Sam

Okay Sam, I've written some code that I think will work for you. Some assumptions I made:
1. The location of the incremental number in your file (C:\my files\work orders.xls) is in cell A1 in Sheet1.
2. You have declared a variable, named File_Number, that captures the new incremental number.
Have a try at this and let me know if it works for you.
Sub Assign_Number()

Dim File_name As String

'This assumes that your incremental number is in cell A1, Sheet1
'and that the number you use is a variable called File_Number

File_name = ActiveWorkbook.FullName

' Your code that saves the file using the numbering system
' is inserted here

Application.DisplayAlerts = False
Workbooks.Open (File_name)
Sheets("Sheet1").Select
Range("A1").Value = File_Number
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = True

End Sub

Regards,
Barrie

File_name = ActiveWorkbook.FullName

Thanks Barrie, I wasn't able to use all the code you wrote. I was able to use a few lines of it added to mine to get the end result I wanted.

Thanks again, Sam