Macro to Cut/PasteSpecial/FileSave

[tdbab]

Sorry, but I'm not into programming language, but I can do a simpl
Macro Record and even attach it to a button.

I have a simple worksheet template for reservations at our B&B. As soo
as I enter the last name of the guest, a FormatConditioned sell display
to remind me to save the file, as NAME-YYYYMMDD (name and arrival date)

I can put a forumla into one cell that displays that intended fil
name; I can copy it and paste-special-value into another cell. I ca
copy that cell and hit SAVE and Ctl-V that as the name of the file, an
save it.

I can do all that manually, but when I try it as a macro, it tries t
save the file as the name of the LAST guest it did that to. If I'
saving JONES-20070106 it asks if I want to replace SMITH-20061221.

When I look at the debug code (god forbid - it's like opening the hoo
of my car!) I see SMITH-20061221 imbedded in the macro!

What am I missing?

Thanks for any help!

Jim / Sleepless in Toront

 

[tdecker81]

Hello,

I can't say that I'll be able to help for sure but it would help if I
could see the code. When it says debug it opens up a new box with a
bunch of stuff commands and stuff in it select the section that applys
to the part you are having trouble with and paste it into a box on
here. I'll check back and see what I can do.

-Tim

 

[tdbab]

Here's a look at the code. The last time I tested it, I tried Mr Bobson
arriving today.
======================

Range("C11").Select
Selection.Copy
Range("D20").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "BOBSON-20060731"
ActiveWorkbook.SaveAs Filename:= _
"D:\My Documents\Excel Files\Guests\BOBSON-20060731.xls"
FileFormat:= _
xlNormal, Password:="", WriteResPassword:=""
ReadOnlyRecommended:=False _
, CreateBackup:=False
Range("B10").Selec

 

[Steel Monkey]

Heres what i would do

IF you have a cell where the the file name you wish the file to be
called is located then enter the following line beneath the first line
of code(will probably be something like sub Macro1() )

dim filename as String

filename = Range("A1") 'where A1 is the cell where the file name is
'change this to whatever the cell is where the file name is located.

then change the following line in your code:
"D:\My Documents\Excel Files\Guests\BOBSON-20060731.xls", FileFormat:=
_
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False

to

"D:\My Documents\Excel Files\Guests\" & Filename , FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False

see how you go with this, hope it makes sense!

 

[tdbab]

Thanks for the attention.

I tried it and it ran aground. It put the BOBSON stuff into the cell
D20 that is where I wanted the new file name (WONDER-20070802)

Notice this line is still in the code (after I put in your code):
ActiveCell.FormulaR1C1 = "BOBSON-20060731"

This is what the whole code looks like now:
----------

Sub SaveFile()
Dim filename As String

filename = Range("D20") 'where A1 is the cell where the file name is '
' SaveFile Macro
' Macro recorded 7/31/2006 by User
'

'
Range("C11").Select
Selection.Copy
Range("D20").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "BOBSON-20060731"
ActiveWorkbook.SaveAs filename:= _
"D:\My Documents\Excel Files\Guests\" & filename, FileFormat:=
_
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
Range("B10").Select
End Sub

 

[Steel Monkey]

Give this a whirl

Sub SaveFile()

Dim filename As String


Range("C11").Select
Selection.Copy
Range("D20").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
filename = Range("D20")
ChDir "D:\My Documents\Excel Files\Guests"
ActiveWorkbook.SaveAs filename:= _
"D:\My Documents\Excel Files\Guests\" & filename, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
Range("B10").Select
End Sub

 

[JON JON]

I think you already got the idea.

Try deleting the line of code ActiveCell.FormulaR1C1 = "BOBSON-20060731"
and see what happen

 

[tdbab]

B-r-i-l-l-i-a-n-t-!- -Absolutely brilliant!

I am so grateful to all of you for your help - here's $50/night of
your next stay at Toronto Downtown Bed and Breakfast!

http://www.TDBAB.com


THANKS MOST SINCERELY
Ji