Macro to save a file as

Y

ynissel

My issue is that I have a macro to copy data from one file to another - but
the first file can have different names.
So - How do I create a macro in Excel to save the file with a new file name,
or how do I make the file that I start the macro in - read the current file
name into the macro so it keeps toggling back to this file. The second file
will always have the same name.

Thanks,
Yosef
 
J

JMB

You will need to edit the macro code. Go to Tools/Macro/Visual Basic Editor.

You will see a project window listing the open workbooks. Find your
workbook and you'll see a folder called "Modules" under it. Expand this
folder and look through the modules to find your macro.

If you used the macro recorder to make your macro, the macro will likely
reference your workbook as Windows(workbook name).Activate - with your
workbook name in quotes (or it could be Workbooks(workbook name).Activate).
Either way, wherever it references your workbook, you can change it to
ThisWorkbook.Activate (no quotes) Then it will not matter what your workbook
is named as Excel will know which workbook is running the macro.
 
Y

ynissel

Im not sure I understand. My macro takes a cell from this file and saves it
to another file then it moves back to this file to copy another cell and so
on...
If I use active ThisWorkbook.Activate (no quotes) - wont it active the
second spreadsheet I am in ?
Here are a few lines of my code where lock-in form is the file I am copying
from and locked loan log is the file I am copying to.
Windows("Lock-in Form.xls").Activate
ActiveCell.Offset(2, 0).Range("A1:B1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("locked loan log.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
Windows("Lock-in Form.xls").Activate
ActiveCell.Offset(2, 0).Range("A1:B1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("locked loan log.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
Windows("Lock-in Form.xls").Activate

Also - (I posted this seperatly) do you know the code to have the macro save
this file as a PDF (using a specific cell as the file name) ?

Thanks,
Yosef
 
J

JMB

Maybe I misunderstood. I am assuming that the macro is located in "Lock-In
Form.xls", whose name may change. You are copying data to "locked loan
log.xls", whose name will not change. If this is the case, wherever your
code says Windows("lock-in form.xls").Activate, you can change to
ThisWorkbook.Activate (which activates whatever workbook is running your
macro - which, again, I assume is "lock-in form." If "locked loan log.xls"
name never changes you would leave the Windows("locked loan log.xls") alone.

Code would look like:

ActiveCell.Offset(2, 0).Range("A1:B1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("locked loan log.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
ThisWorkBook.Activate
ActiveCell.Offset(2, 0).Range("A1:B1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("locked loan log.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
ThisWorkBook.Activate


I don't know how to get the data into a pdf (Adobe Acrobat file - right?).
I only have the Adobe reader (free) which I understand does not let me create
acrobat files. You may have to buy the full Adobe program to create a pdf
file (I'd go to Adobe.com to learn more about it).
 
Y

ynissel

Thanks - Ill try it !

JMB said:
Maybe I misunderstood. I am assuming that the macro is located in "Lock-In
Form.xls", whose name may change. You are copying data to "locked loan
log.xls", whose name will not change. If this is the case, wherever your
code says Windows("lock-in form.xls").Activate, you can change to
ThisWorkbook.Activate (which activates whatever workbook is running your
macro - which, again, I assume is "lock-in form." If "locked loan log.xls"
name never changes you would leave the Windows("locked loan log.xls") alone.

Code would look like:

ActiveCell.Offset(2, 0).Range("A1:B1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("locked loan log.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
ThisWorkBook.Activate
ActiveCell.Offset(2, 0).Range("A1:B1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("locked loan log.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
ThisWorkBook.Activate


I don't know how to get the data into a pdf (Adobe Acrobat file - right?).
I only have the Adobe reader (free) which I understand does not let me create
acrobat files. You may have to buy the full Adobe program to create a pdf
file (I'd go to Adobe.com to learn more about it).
Click to expand...
 
Top