auto saving a file using a number value from a cell in the spreadsheet

[mpfyffe]

I have created a macro that worked perfect before I added the final step. I wanted to add a new number to a list of my list of numbers then copy two cells and paste them to the next row then copy the new number from the list and open a spreadsheet and add the new number to a particular cell. (up to this point it works fine) the last and final step I want my macro to do is save the blank estimate with a numbered file name based on the number I pasted into the opened spreadsheet from the first spreadsheet, the value of the number is 8 digits but the format includes dashes between the first two the second two and the last four digits (13500099 = 13-50-0099) and I need the dashes to appear in the file name too.

See my code below:

Sub StartNextEstimate()
'
' StartNextEstimate Macro
'

'
Application.Goto Reference:="R65536C1"
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
ActiveCell.Offset(-1, 7).Range("A1:B1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, -7).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ChDir "C:\Users\michaelf\Documents\Macro practice"
Workbooks.Open Filename:= _
"C:\Users\michaelf\Documents\Macro practice\estimate.xlsm", UpdateLinks:=3
Range("A1").Select
ActiveCell.Offset(5, 1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
(here is where I would like to add code that would take the value of the cell and include the dashed format and use that value to name and save the new file)
End Sub

[SamT]

Name = Range("A1").End(xlDown).Text

[mpfyffe]

Thanks, so this will assign the variable Name the text value of the cell, how then do I use the Name variable in a save as function to rename the document using that text value?

[SamT]

"Name" can not be a variable. "BkName," can be. Name is both a Property and an Object in VBA.

My bad.
Name = Range("A1").End(xlDown).Text Will not work for you because it fails if there are no values below "A1."

This will work in all cases.
Range("A:" & LastRow -1).Text
Dim LastRow
LastRow = Cells(Rows.Count, 1). End(xlUp))

I did spend some time looking at your code, but by holding down the shift key so much while recording it, it became almost impossible to decipher. I know that you are trying to increment the value in "A" by 1 for each Row.

Application.Goto Reference:="R65536C1"
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
That can be done with
Range("A" & LastRow) = Range("A" & LastRow).Offset(0, -1)
However, you use that cell reference a few more times in your code, so, let's assign it to a variable, rngEstID.
Dim rngEstID As Range
Set rngEstID = Range("A" & LastRow)
rngEstID = rngEstID.Offset(0, -1)
The next time you refer to it is when you copy down the values in "H" & "I"
ActiveCell.Offset(-1, 7).Range("A1:B1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
Since I don't know what values those hold I am using the variable "Mystery"
Dim rngMystery As Range
LastRow = Cells(Rows.Count, 8). End(xlUp))
Set rngMystery = Range("H" & LastRow-1).Range("A1:B1")
rngMystery.Copy rngMystery.Offset(1)
Depending o how many times you refer to the new book, you can use
With Workbooks.Open Blah,Blah, blah,
'code here
SaveAs(Blah,blah,blah, Name = rngEstId.Txt)
End With OR
Dim NewSet As Workbook
Set NewEst = Workbooks.Open Blah,Blah, blah,
rngMystery.Offset(1).Copy
NewEst.Sheets(1).Range("B6").PasteSpecial Paste:=xlValues
'more code here
Since you include the full Path in the Workbooks.Open method, you don't need the ChDir statement.

The easiest method I have found to get help on a VBA subject, is to place the cursor in, or next to , the word I want and press F1. I suggest that you try this with "Copy" and "SaveAs."
Placing "Option Explicit" as the top line in VBA will help you with speeling errors. Pressing F5 while the cursor is in a macro will Run the macro. Pressing F8 will Step through the macro. Using the VBA Menu >> Debug >> Compile will find more errors for you.

While Stepping through the macro, if you place the Cursor over any Variable in the code lines before the highlighted line, you can see the value of that variable. If any errors occur, they will be in the line(s) before the Highlighted one. The highlighted line is the next line to run.

Nothing herein has been tested for accuracy. You will need to use F1 a lot.:devil2:

[mpfyffe]

Thank you so much for putting in so much effort to help me. I will have to re-read your post several times to be sure I understand it. In the mean time I will attach the file that contains my list of estimates its file name is BidLog.xlsm and the template I want to use for the blank estimate that gets opened its file name is BlankEstimate.xlsm The macro is written in the BidLog.xlsm and I want to put the value and the format dashes in the new estimate file created with the save as function and it will be named 13-50-0000.xls only the last four digits will ever change so if that makes it easier for saving the estimate with a name that contains dashes and numbers I could add columns to the BidLog.xlsm to keep the four digits separate from the first four and the dashes. I just didn't know what would be the easiest and most code friendly to set up. (example: column A can contain just the number 13 in every cell, column B & D can contain just a dash, and column C can contain just the number 50 in every cell, and column E can contain the four digit number that increases with each new entry in the BidLog.xlsm) - But if my bosses like the way I do this they may want me to make the changes in the documents company wide and then the number 50 would be different for every division and the number 13 will change every year, next year it will be 14... I will have to take some time to make a copy of the files I want to attach because I need to change the real data to generic data to protect company/client security.

[mpfyffe]

How can I attach the files to this forum thread?

[mpfyffe]

I figured out how to upload the file here is the first one.

[mpfyffe]

Here is the file where the macro is written, the first file is the one I have the macro open and fill in data

[SamT]

Well, mpfyffe, you came to the right guy, at least for this stage. I was in the building trades for over 20 years, many of them as a contractor. I don't know how many Excel Estimating systems I've designed.

To answer your particular question first, that EstID is fine. It is merely the Strings:

Yr = Format (Now, yy)
DivID = "50". Should be a reference to a cell that holds each Divisions ID# as Text, but you can use CStr(50) if the ID is numerical, or you can pull it out of a longer DivID String.
SeqNum = Right(EstID, 7,4)
To generate the next EstID is
EstID = Yr & "-" & DivID & "-" & CStr(Format(CLng(SeqNum)) + 1, 0000))Note that this will work for any year and any Division.

Back to the Estimating system's design. While you are dsigning the layout and structure of the system is a very good time to think about how the data will be used in any code, but it is still too early to really worry about the code itself.

Code Flows from Data Structure. Not vice versa.

In the interest of KISS from a coders viewpoint and keeping it easy for various Users to use, and most especially, keeping it easy for the bosses to get the information they need as easy as possible, I recommend that your workbooks be:

The Master Company Logbook:
Should have as the first sheet, a Managers Financial Report that shows the staus of various particulars and the money status; WIP, completed, in bid, rejected, number of leads waiting appointments, Apps waiting estimates, etc. Talk to them and see what they want.

Then it should contain a Master Log sheet for each division, from which all the info in the Financial report sheet is pulled.

This book can be automatically updated, say at midnight of each day, or on demand.

Where you keep the client DB is up to you, one master list or a separate list for each division, kinda depends on geography.

Each Division should have several books.

The LogBook of course, generally as you have it now, but I suggest you create a specific header for it that includes the Division ID as a fixed value, and with Cell Format = Text.

An Estimating Template book.
This book is completely formatted and may have "macros" in it, but has no data, but is saved as a new estimate as needed. Any "macros" it has are specific to creating an estimate's numbers, and entering those numbers in the Log Book. It is probably identical across all divisions. Its division specific data is filled out by and with data from...

The Division Master Estimating book.
Until a new Estimating book gets in the hands of an estimator, this book is the one doing all the work.

In Format, this book is identical across divisions, but it contains specific data for each division, ie, employee data. It is responsible for getting and logging all estimating date in the Log book. It contains all 'macros' required to complete the Estimating Template and save it

The Price (Cost) Book
Can be a master or Division specific. It is used by the 'macros' in the Estimating Template book, after it has been saved under a new name.

Note how every part of the system described above is specific to a single phase. The more modular you can keep the system, the easier it will be to maintain, remodel, and, most importantly, understand.


It is the end of my day, and my brain is running out of this morning's coffee, There is a lot to think about herein and I hope we here from you again soon.

[mpfyffe]

Thanks a lot SamT, I am not designing any new files I am just taking what the company already has and trying to edit it as little as possible and automate the data entry. My superintendents have to enter the same data into four files every time we have a client decide to purchase one of our estimates. I started out thinking this would be an easy project to automate the data entry but over 50 hours later here I am. I am up for a promotion and I wanted to take in the four files I am trying to automate in order to demonstrate my usefulness to the company and my qualifications as a project engineer.

[SamT]

Use a UserForm. They can enter it into one Form and when pressing a command button the code in the form can update all the needed files. Some items in the Form can be pulled from existing files.

It is kinda frustrating trying to help when every response generates new information and requirements.

Now I know that 90% of my last post is wasted time.

Anyway, you know how to handle the issue in your original post.

[mpfyffe]

I am sorry if I have frustrated you, but everything you have written has been extremely helpful so I am very grateful for your assistance. Thank you very much.