Macro to Cut/PasteSpecial/FileSave

[tdbab]

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

I have a simple worksheet template for reservations at our B&B. As soon as I enter the last name of the guest, a FormatConditioned sell displays 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 file name; I can copy it and paste-special-value into another cell. I can copy that cell and hit SAVE and Ctl-V that as the name of the file, and save it.

I can do all that manually, but when I try it as a macro, it tries to save the file as the name of the LAST guest it did that to. If I'm 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 hood of my car!) I see SMITH-20061221 imbedded in the macro!

What am I missing?

Thanks for any help!

Jim / Sleepless in Toronto

[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").Select

[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"

I got a Runtime Error "1004" - The file could not be accessed. And the last few lines of my code, on debug, were yellow highlighted:

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

The above section was yellow highlighted.

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> tdbab
> ------------------------------------------------------------------------
> tdbab's Profile:
> http://www.excelforum.com/member.php...o&userid=36937
> View this thread: http://www.excelforum.com/showthread...hreadid=566502
>

[tdbab]

Brilliant! Absolutely brilliant!

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

http://www.TDBAB.com


THANKS MOST SINCERELY
Jim