Tweaking Macros

[Nick H]

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.

 

[Don Guillett]

Post ALL of your code

 

[JoeU2004]

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]

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 7/5/2009 by Nick's Laptop
'
' Keyboard Shortcut: Ctrl+Shift+T
'
Application.Goto Reference:="R16C4"
Range("D16:Q16").Select
Application.CutCopyMode = False
Selection.Copy
Selection.End(xlToLeft).Select
Selection.End(xlDown).Select
Range("D1282").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.Goto Reference:="R3C6"
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
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
End Sub

 

[Howard31]

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]

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.

 

[Jacob Skaria]

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