Updating Excel sheet when using another Excel sheet as a template

[pemco]

I'm using Excel 2003.
I have an Excel spreadsheet that I'd like to use as a template for quotations for my company.
Our quotes manager will enter the information into the spreadsheet, do a "save as" each time he quotes a job. The original sheet will be used as the template.
Is there a way to dynamically collect the data from the first sheet (even though it'll be saved as something different each time) into another Excel sheet that can be used for tracking open quotes?
Thanks in advance for any help!

 

[NdNoviceHlp]

Hi pemco and Welcome to the Board! I would suggest using this code. No need to save the template with its' data ie. it can be cleared for the next input. The code saves the workbook to the same file location with the same file name and date. It can be adjusted to input a file name instead of the date and/or same file name. HTH. Dave

Sub CopyDateFile()
Dim Ofsobj As Object, NowStr As String
Set Ofsobj = CreateObject("Scripting.FileSystemObject")
NowStr = Format(Now, "yyyy/mm/dd")
Ofsobj.CopyFile ThisWorkbook.FullName, Left(ThisWorkbook.FullName, _
   Len(ThisWorkbook.FullName) - 5) & "_" & NowStr & ".xlsm", True  'source,destination,save
Set Ofsobj = Nothing
End Sub

ps. U may need to change the file extension ".xlsm" to whatever

 

[JLGWhiz]

Hi pemco and Welcome to the Board! I would suggest using this code. No need to save the template with its' data ie. it can be cleared for the next input. The code saves the workbook to the same file location with the same file name and date. It can be adjusted to input a file name instead of the date and/or same file name. HTH. Dave

Sub CopyDateFile()
Dim Ofsobj As Object, NowStr As String
Set Ofsobj = CreateObject("Scripting.FileSystemObject")
NowStr = Format(Now, "yyyy/mm/dd")
Ofsobj.CopyFile ThisWorkbook.FullName, Left(ThisWorkbook.FullName, _
   Len(ThisWorkbook.FullName) - 5) & "_" & NowStr & ".xlsm", True  'source,destination,save
Set Ofsobj = Nothing
End Sub

ps. U may need to change the file extension ".xlsm" to whatever

If the OP is using xl2003 then this might work better

Len(ThisWorkbook.FullName) - [COLOR=#FF0000]4[/COLOR])

 

[pemco]

Hi pemco and Welcome to the Board! I would suggest using this code. No need to save the template with its' data ie. it can be cleared for the next input. The code saves the workbook to the same file location with the same file name and date. It can be adjusted to input a file name instead of the date and/or same file name. HTH. Dave

Sub CopyDateFile()
Dim Ofsobj As Object, NowStr As String
Set Ofsobj = CreateObject("Scripting.FileSystemObject")
NowStr = Format(Now, "yyyy/mm/dd")
Ofsobj.CopyFile ThisWorkbook.FullName, Left(ThisWorkbook.FullName, _
   Len(ThisWorkbook.FullName) - 5) & "_" & NowStr & ".xlsm", True  'source,destination,save
Set Ofsobj = Nothing
End Sub

ps. U may need to change the file extension ".xlsm" to whatever

Thanks so much for the help! Last night, our IT guy upgraded our entire system to Windows 365. He also updated my old office suite to 2016. Will that affect the code?
Additionally: I'm a novice when it comes to codes. Where do I enter this code...in the template worksheet or the data collection worksheet? And, where exactly do I enter it? Do I need to change anything within the code to recognize the template's file name?
I apologize for my ignorance. This is way beyond my comfort level! I can handle basic Excel functions, but beyond that, I'm toast! Thanks so much for any and all guidance!

 

[NdNoviceHlp]

U can place this in sheet/userform or module code. Just call/run CopYDateFile. No need to change anything for the file name as it is copied as the name of the file along with the date to the same file path/location. This is Xl 2016 code. Good luck. Dave

 

[JLGWhiz]

Thanks so much for the help! Last night, our IT guy upgraded our entire system to Windows 365. He also updated my old office suite to 2016. Will that affect the code?
Additionally: I'm a novice when it comes to codes. Where do I enter this code...in the template worksheet or the data collection worksheet? And, where exactly do I enter it? Do I need to change anything within the code to recognize the template's file name?
I apologize for my ignorance. This is way beyond my comfort level! I can handle basic Excel functions, but beyond that, I'm toast! Thanks so much for any and all guidance!

The way the code is written, it would be most efficient to run from a standard code module like module1. To access the code module, press Alt + F11 and when the vb editor opens, if the large pane is dark, click 'Insert' on the menu bar of the vb editor. Then click 'Module'. The pane should then brighten and you can copy the code and paste it right into the large pane. Close the vb editor and save the workbook as a macro enabled workbook (.xlsm) to preserve the code. To run the code from the Excel window, press Alt + F8, left click the macro name once in the dialog box, then click 'Run'. To attach the macro to a button, on the ribbon go to 'Developer', click 'Insert' on the ribbon menu bar, then click on command button (or just button) in the Form Controls tool box. Move the cross hair (+) to where you want the button to appear on your sheet and left click once. A dialog box will appear to assign a macro, click on the macro name then click OK. You can now run the code by clicking the button on your sheet.

 

[pemco]

Thanks for all the help!
I've copied the code into my worksheet and followed the steps outlined above. When I run the macro I get an error message. When I debug, the following is hi-lighted:
Ofsobj.CopyFile ThisWorkbook.FullName, Left(ThisWorkbook.FullName, _ Len(ThisWorkbook.FullName) - 5) & "_" & NowStr & ".xlsm", True 'source,destination,save

In researching a bit, I found the "Get Data" option in Excel 2016, which, in theory seems like it should work for me. When I use this, I can see the columns that I need, but they're randomly scattered throughout the worksheet and I'm unable to arrange them as I'd like.
As I mentioned initially, I'm using the initial Excel sheet as a template, so each time a sales team member enters his/her quote, they'll do a save as and then delete the information from the template. I'm concerned that this will cause an issue.

Please forgive my ignorance. I've never worked with macros or codes before and I'm somewhat clueless.

Thanks so much for helping me!

 

[NdNoviceHlp]

The code works as posted. Don't know what's up? No idea what's with "Get Data" part of your post? Dave