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
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,721
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,721
Office Version
2010
Platform
Windows
You're welcome, good luck.
 

Forum statistics

Threads
1,084,856
Messages
5,380,319
Members
401,664
Latest member
traveler84

Some videos you may like

This Week's Hot Topics

Top