Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tweaking Macros
I've recorded a macro, and need to make some adjustments but after a couple hours trying to find it in the Microsoft VBA documentation, I've given up and hope someone here can help me. First question, here is a portion of the code as recorded: Selection.End(xlToLeft).Select Selection.End(xlDown).Select Range("D1282").Select The first two steps put the active cell on the last cell containing data in column B. The next thing I did in recording was to press the right arrow twice. The third line above indicates a specific cell is now the active cell; what I want is for the active cell to be two columns to the right of the last cell in my data table. (The data table varies in length, so it won't always be row 1282.) Later, I want to save the file with a new file name. The file name is a text entry in a cell in my spreadsheet. This worked OK the first time I ran the macro, but the next time I got a warning that the file already existed (ie, it did not update). Here is the code that was generated; the file name I want always resides in cell F3 of the active worksheet: ActiveWorkbook.SaveAs Filename:= _ "C:\Documents and Settings\Nick\My Documents\UNT\Physics 6750\Data Anlaysis\N=0,100, k=4, p=0.xls" _ , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False Any help would be appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tweaking Macros
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tweaking Macros
"Nick H" wrote: First question, here is a portion of the code as recorded: Selection.End(xlToLeft).Select Selection.End(xlDown).Select Range("D1282").Select [....] what I want is for the active cell to be two columns to the right of the last cell in my data table. If that's what you want, shouldn't you use xlToRight instead of xlToLeft? In any case, the following might do what you want: dim rng as Range Selection.End(xlToLeft).Select Selection.End(xlDown).Select Set rng = Selection rng.Cells(1, 3).Select Later, I want to save the file with a new file name. The file name is a text entry in a cell in my spreadsheet. This worked OK the first time I ran the macro, but the next time I got a warning that the file already existed Try the following: fname = Range("A1") If ActiveWorkbook.FullName = fname Then ActiveWorkbook.Save Else If Dir(fname) < "" Then Kill fname ActiveWorkbook.SaveAs Filename:= fname, FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False End If That assumes that A1 contains the full path and file name. This is not bullet-proof. If fname is open, but not the active workbook, Kill will fail. ----- original message ----- "Nick H" wrote in message ... I've recorded a macro, and need to make some adjustments but after a couple hours trying to find it in the Microsoft VBA documentation, I've given up and hope someone here can help me. First question, here is a portion of the code as recorded: Selection.End(xlToLeft).Select Selection.End(xlDown).Select Range("D1282").Select The first two steps put the active cell on the last cell containing data in column B. The next thing I did in recording was to press the right arrow twice. The third line above indicates a specific cell is now the active cell; what I want is for the active cell to be two columns to the right of the last cell in my data table. (The data table varies in length, so it won't always be row 1282.) Later, I want to save the file with a new file name. The file name is a text entry in a cell in my spreadsheet. This worked OK the first time I ran the macro, but the next time I got a warning that the file already existed (ie, it did not update). Here is the code that was generated; the file name I want always resides in cell F3 of the active worksheet: ActiveWorkbook.SaveAs Filename:= _ "C:\Documents and Settings\Nick\My Documents\UNT\Physics 6750\Data Anlaysis\N=0,100, k=4, p=0.xls" _ , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False Any help would be appreciated. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tweaking Macros
Hi Nick,
Replace "Range("D1282").Select" with "ActiveCell.Offset(0,2).Select" About saving the workbook...F3 will have to have a diferent value each time you save and the workbook will have to be saved under the same name first with the new value, so before the code that saves with a new name put the following code: ThisWorkbook.Save Hope this helps "Nick H" wrote in message ... I've recorded a macro, and need to make some adjustments but after a couple hours trying to find it in the Microsoft VBA documentation, I've given up and hope someone here can help me. First question, here is a portion of the code as recorded: Selection.End(xlToLeft).Select Selection.End(xlDown).Select Range("D1282").Select The first two steps put the active cell on the last cell containing data in column B. The next thing I did in recording was to press the right arrow twice. The third line above indicates a specific cell is now the active cell; what I want is for the active cell to be two columns to the right of the last cell in my data table. (The data table varies in length, so it won't always be row 1282.) Later, I want to save the file with a new file name. The file name is a text entry in a cell in my spreadsheet. This worked OK the first time I ran the macro, but the next time I got a warning that the file already existed (ie, it did not update). Here is the code that was generated; the file name I want always resides in cell F3 of the active worksheet: ActiveWorkbook.SaveAs Filename:= _ "C:\Documents and Settings\Nick\My Documents\UNT\Physics 6750\Data Anlaysis\N=0,100, k=4, p=0.xls" _ , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False Any help would be appreciated. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tweaking Macros
Thanks for your quick response! The first part makes sense; I think I'll be
fine with that. With regard to the file save, the workbook that I'm starting with is a template of sorts. My data is generated in Matlab; I copy the data from there into the Excel workbook, then run the macro to do the calculations and save the workbook with a unique filename. Then, I open the template again, and paste in the next dataset, and so on. So, I don't really want to save the workbook before the save to the new file name. All I really want is to save to a file with a name that is contained in a particular cell on spreadsheet. Can you explain how I do that? Thanks again. "Howard31" wrote: Hi Nick, Replace "Range("D1282").Select" with "ActiveCell.Offset(0,2).Select" About saving the workbook...F3 will have to have a diferent value each time you save and the workbook will have to be saved under the same name first with the new value, so before the code that saves with a new name put the following code: ThisWorkbook.Save Hope this helps "Nick H" wrote in message ... I've recorded a macro, and need to make some adjustments but after a couple hours trying to find it in the Microsoft VBA documentation, I've given up and hope someone here can help me. First question, here is a portion of the code as recorded: Selection.End(xlToLeft).Select Selection.End(xlDown).Select Range("D1282").Select The first two steps put the active cell on the last cell containing data in column B. The next thing I did in recording was to press the right arrow twice. The third line above indicates a specific cell is now the active cell; what I want is for the active cell to be two columns to the right of the last cell in my data table. (The data table varies in length, so it won't always be row 1282.) Later, I want to save the file with a new file name. The file name is a text entry in a cell in my spreadsheet. This worked OK the first time I ran the macro, but the next time I got a warning that the file already existed (ie, it did not update). Here is the code that was generated; the file name I want always resides in cell F3 of the active worksheet: ActiveWorkbook.SaveAs Filename:= _ "C:\Documents and Settings\Nick\My Documents\UNT\Physics 6750\Data Anlaysis\N=0,100, k=4, p=0.xls" _ , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False Any help would be appreciated. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tweaking Macros
Do you mean;
strFile = Range("A1") ActiveWorkbook.SaveAs Filename:= _ "C:\Documents and Settings\Nick\My Documents\UNT\Physics 6750\Data Anlaysis\" & strFile & ".xls" _ , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False If this post helps click Yes --------------- Jacob Skaria "Nick H" wrote: Thanks for your quick response! The first part makes sense; I think I'll be fine with that. With regard to the file save, the workbook that I'm starting with is a template of sorts. My data is generated in Matlab; I copy the data from there into the Excel workbook, then run the macro to do the calculations and save the workbook with a unique filename. Then, I open the template again, and paste in the next dataset, and so on. So, I don't really want to save the workbook before the save to the new file name. All I really want is to save to a file with a name that is contained in a particular cell on spreadsheet. Can you explain how I do that? Thanks again. "Howard31" wrote: Hi Nick, Replace "Range("D1282").Select" with "ActiveCell.Offset(0,2).Select" About saving the workbook...F3 will have to have a diferent value each time you save and the workbook will have to be saved under the same name first with the new value, so before the code that saves with a new name put the following code: ThisWorkbook.Save Hope this helps "Nick H" wrote in message ... I've recorded a macro, and need to make some adjustments but after a couple hours trying to find it in the Microsoft VBA documentation, I've given up and hope someone here can help me. First question, here is a portion of the code as recorded: Selection.End(xlToLeft).Select Selection.End(xlDown).Select Range("D1282").Select The first two steps put the active cell on the last cell containing data in column B. The next thing I did in recording was to press the right arrow twice. The third line above indicates a specific cell is now the active cell; what I want is for the active cell to be two columns to the right of the last cell in my data table. (The data table varies in length, so it won't always be row 1282.) Later, I want to save the file with a new file name. The file name is a text entry in a cell in my spreadsheet. This worked OK the first time I ran the macro, but the next time I got a warning that the file already existed (ie, it did not update). Here is the code that was generated; the file name I want always resides in cell F3 of the active worksheet: ActiveWorkbook.SaveAs Filename:= _ "C:\Documents and Settings\Nick\My Documents\UNT\Physics 6750\Data Anlaysis\N=0,100, k=4, p=0.xls" _ , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False Any help would be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro tweaking | Excel Discussion (Misc queries) | |||
Tweaking formula? | Excel Discussion (Misc queries) | |||
Tweaking Code | Excel Programming | |||
Tweaking Code | Excel Programming | |||
Formula needs tweaking - HELP!! | Excel Programming |