Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Cut/PasteSpecial/FileSave
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 -- tdba ----------------------------------------------------------------------- tdbab's Profile: http://www.excelforum.com/member.php...fo&userid=3693 View this thread: http://www.excelforum.com/showthread.php?threadid=56650 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Cut/PasteSpecial/FileSave
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 -- tdecker81 ------------------------------------------------------------------------ tdecker81's Profile: http://www.excelforum.com/member.php...o&userid=36938 View this thread: http://www.excelforum.com/showthread...hreadid=566502 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Cut/PasteSpecial/FileSave
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 -- tdba ----------------------------------------------------------------------- tdbab's Profile: http://www.excelforum.com/member.php...fo&userid=3693 View this thread: http://www.excelforum.com/showthread.php?threadid=56650 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Cut/PasteSpecial/FileSave
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! -- Steel Monkey ------------------------------------------------------------------------ Steel Monkey's Profile: http://www.excelforum.com/member.php...o&userid=29051 View this thread: http://www.excelforum.com/showthread...hreadid=566502 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Cut/PasteSpecial/FileSave
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Cut/PasteSpecial/FileSave
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 -- Steel Monkey ------------------------------------------------------------------------ Steel Monkey's Profile: http://www.excelforum.com/member.php...o&userid=29051 View this thread: http://www.excelforum.com/showthread...hreadid=566502 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Cut/PasteSpecial/FileSave
I think you already got the idea.
Try deleting the line of code ActiveCell.FormulaR1C1 = "BOBSON-20060731" and see what happen "tdbab" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Cut/PasteSpecial/FileSave
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 -- tdba ----------------------------------------------------------------------- tdbab's Profile: http://www.excelforum.com/member.php...fo&userid=3693 View this thread: http://www.excelforum.com/showthread.php?threadid=56650 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with PasteSpecial | Excel Programming | |||
PasteSpecial xlPasteFormats ends macro | Excel Programming | |||
Range Name Duplication and PasteSpecial Macro | Excel Programming | |||
PasteSpecial | Excel Programming | |||
PasteSpecial macro | Excel Programming |