How to create a copy of a worksheet and automatically add the date to the worksheet name

Hi all,

The following is my process flow and what i need:

1. User selects to import data contained in external sheets
2. That data is placed in one of two hidden worksheets and tables created for the data there (rooms and/or materialTotals)
3. User selects macro to create new estimate (this macro has not been established yet, hense part of my question)
4. Using the data contained in those tables, along with some user input an estimate is created in worksheet (Estimate Template).
5. After user confirmation has indicated that the estimate is completed, then all three worksheets (rooms, materialTotals, Estimating Template) or the values contained are copied to new worksheets (Worksheet name + Date).
6. User saves/closes file
7. Upon reopening the file if the user wants to create a new estimate then the process flow 1-5 should be followed, with the following exception: Data from the previous estimate sheet should be imported into the 'new' estimate template.  The worksheet template files should never have data from previous imports contained in them.

Everything is in place, with the exception of 3-5, which is why I'm here.  With the help of some very knowledgeable people (dlmille) here i have been able to exponentially expand my ability to use VBA, however this goes well beyond my current capabilities.  Please don't hesitate to ask my any questions that may be able to further clarify my intentions.


Viewing the attached file would be the easiest way to 'see' whats going on.
TestingCopy-r2.xlsm
DadXSixAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TommySzalapskiCommented:
Here's code that will do what your title question asks.
Dim srcName As String
Dim wsCopy As Worksheet

srcName = "Sheet2" 'Change this to the name of the source sheet

Worksheets(srcName).Copy , Worksheets(Worksheets.Count)
Set wsCopy = Worksheets(Worksheets.Count)

wsCopy.Name = srcName & " " & Format(Date, "yyyy-mm-dd")

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dlmilleCommented:
So let me paraphrase what I think you're asking for:

You want a macro based on the button "Create New Estimate" that will make a copy of this workbook (all tabs?) and allow the user to make changes to the tabs in that new workbook that are currently hidden.  The new workbook name will be ThisWorkbook + date (assume mm-dd-yyyy?  is time important?).

The reason I ask if all tabs, is that Material Database/Admin are needed for the Data Validation work, correct?

Then, when the file is reopened, the user can do any manipulation wanted, and the original data is propogated, because it was copied in the first step.

I'm still a bit puzzled, so please clarify a bit more...

Dave
0
DadXSixAuthor Commented:
Close, see if the below explanation helps to shed some more light.  I added some notes outlining it in the attached file as well.


Once finished, the workbook we are currently creating will be saved as a template (xltm) file.  If i want to start a new estimate, ill open the template file and save-as "New Job Name".  Any 'estimate' as it relates to the Titled workbook will be saved in said workbook.  

The Only Tabs visible in the workbook will be the Admin tab (this will be changed to more of a summary sheet) and any estimates that have been created.

All three worksheets (rooms, materialTotals and Estimate) would comprise a new estimate.  The data being imported into the rooms/materialTotals tables would only be specific to the estimate thats being worked on at the moment.

My 1st thought, after some research, was to create a worksheet array using the three mentioned sheets.  Naming this array and using this information is where i more or less hit the stumbling block.


TestingCopy-r2-comments.xlsm
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

dlmilleCommented:
Ok - sorry I didn't get to this before now.  I see a lot of action you want to take - exactly what assistance is required?

What do you want to do with the array of sheet names?

Dave
0
dlmilleCommented:
Here's some code for handling your array of sheets - one way :)

Sub Demo()
Dim estTemplate As Variant

    Set estTemplate = Sheets(Array("Estimating Template", "rooms", "materialTotals"))
   
    For i = 1 To estTemplate.Count 'if you WATCH esttemplate in the debug window, you'll see its propertiesDebug.Print estTemplate(0).Name
        Debug.Print estTemplate(i).Name
        estTemplate(i).Activate 'so note estTemplate(i) is the handle to the sheet part of the object embedded in this variant
    Next i

End Sub
0
dlmilleCommented:
Perhaps I can best help (if this is working) is to respond to a series of "How do you do..." like I demonstrated with the array of worksheets.  If you could list out the half-dozen +/- I could respond accordingly and you can build what you need.

Would this help, or am I creating frustration?

Dave
0
dlmilleCommented:
Its up to DadXSix.

Dave
0
DadXSixAuthor Commented:
Sorry for the long delay.  I split points between Dave and Tommy, simply because i have forgotten what my original intention with the questions was.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.