Amazon Fire HD 8 review: A brilliant combination of function and value – with one massive caveat
I have a (successfully running) spreadsheet diary with all sorts of odds and ends of macros in it. One of the macros 'copies' just the content of the diary to a single sheet spreadsheet (without any macros or any of the other bits and pieces that the 'original user' has) for subsequent transmission to a 'remote' person. The aim is that the originator can just copy his/her diary to someone else. I call this copy of the diary (very originally) Diary for Transmission.xls
This method has been in use for a few years with no hassle.
BTW The 'original master' diary has a space for the user to enter his/her name in a cell (D3).
Because there is now the possibility that more than one 'originator' may need to send a 'Diary for Transmission.xls' to the same remote location, there will obviously be a problem at the remote end in trying to load more than one 'Diary for Transmission.xls' on to their desktop (or wherever).
What I would like to do is make a macro that automatically changes the name of the transmitted diary to include that users' name (in cell D3) and make a meaningful file name out of it such as 'Diary from Fred Bloggs.xls'.
Can this be done?
As far as I know you'll have to save the file under the required name before sending it anywhere, so how about:
Dim UserName As String
UserName = Cells(1, 1).Value
ThisWorkbook.SaveAs ("Diary From " & UserName)
The resulting name will be Diary From UserName.xls.
name = Range("D3").Value
would be slightly more efficient ;o)
Did it VoG™'s way...
but didn't even use the .Value qualifier!
I ended up making a 'model' that did all sorts of useful things with additional benefits of using the 'path' facility so the master and slave - sorry, owner's and transmission - copies could be anywhere on the computer - as long as they stay in the same folder.
All in all, I'm quite pleased with my afternoon's work! And I was so busy 'fiddling, I didn't notice the additions to the thread!
And, if anyone wants a peep at the full thing, yellow envelope me.
Sometimes you can get away with omitting .Value and sometimes you can't. It isn't easy to predict. I always use it because although it requires an additional 6 keystrokes and makes your file 6 bytes larger, I think that when the code executes the interpreter must have to decide what to do, increasing the execution time. That is purely supposition on my part.
An interesting philosophy!
I developed the bad habit of omitting .Value when I first started playing in Excel 5, I think it was, any way it was when the first attempts at vba were being used. (I hated Excel 4 macro writing!).
And, to be as certain as I can, I don't think that I had a problem.
But, I shall learn from the Master in case it does decrease execution time.
This thread is now locked and can not be replied to.