Using SaveAs but keeping updated information in original

JAlbers

Board Regular
Joined
Sep 9, 2011
Messages
60
I have a master file that will have a transaction number generated, data will be entered by the user and lookups will be performed on the data they enter. The file is then going to be saved in a named folder using the transaction number generated as the file name.xls.

The transaction number is simply taking a seed number and adding one to it. My problem comes in when I save the file as the new transaction number.xls the original transaction seed number in the master file doesn’t get saved, therefor I end up with all my transaction numbers being the same.

Here is the code snippet I am using to increment the transaction number:

Sub New_MTR_Number()
LastMTR = Worksheets("OldMTRNumber").Range("A1").Value
NextMTR = LastMTR + 1
Worksheets("MTR").Range("E6").Value = NextMTR
Worksheets("OldMTRNumber").Range("A1").Value = NextMTR
End Sub

After saving the newly named file to a transaction folder, this file would be closed, and the new transaction number lost. My thought is to save this master file when the transaction number is incremented, then when the other data has been entered for the lookup, it can be saved using save as to the new file name (transaction number.xls)
Somehow, I feel that I am making this harder than it should be, and I will appreciate any help I can get.
Thanks
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,736
Office Version
2010
Platform
Windows
Welcome to the board.

You could instead use SaveCopyAs, and then save the master in the usual way.
 

JAlbers

Board Regular
Joined
Sep 9, 2011
Messages
60
Thanks! I knew there had to be an easier, more straight-forward way of attacking this.

Thanks again!:)
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,736
Office Version
2010
Platform
Windows
You're welcome, good luck.
 

Watch MrExcel Video

Forum statistics

Threads
1,089,992
Messages
5,411,713
Members
403,394
Latest member
Mohan Kumar

This Week's Hot Topics

Top