Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
ynissel
 
Posts: n/a
Default Macro to save a file as

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
  #2   Report Post  
JMB
 
Posts: n/a
Default

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.





"ynissel" wrote:

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

  #3   Report Post  
ynissel
 
Posts: n/a
Default

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


"JMB" wrote:

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.





"ynissel" wrote:

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

  #4   Report Post  
JMB
 
Posts: n/a
Default

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).




"ynissel" wrote:

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


"JMB" wrote:

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.





"ynissel" wrote:

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

  #5   Report Post  
ynissel
 
Posts: n/a
Default

Thanks - Ill try it !

"JMB" wrote:

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).




"ynissel" wrote:

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


"JMB" wrote:

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.





"ynissel" wrote:

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
macro to save file automaticaly? david Excel Discussion (Misc queries) 1 May 6th 05 05:21 PM
How to stop getting the file save box when running a macro Pank Mehta Excel Discussion (Misc queries) 1 March 29th 05 04:05 PM
Excell2003 (SP-1) File > Save and File > Save As.. grayed out Joe Murphy Excel Discussion (Misc queries) 0 March 9th 05 11:00 PM
This one is tricky....Macro to save file as cell value x in di Andy Excel Discussion (Misc queries) 4 November 26th 04 09:52 AM
Macro did not run after download file from net ariffin Excel Worksheet Functions 2 November 6th 04 02:20 AM


All times are GMT +1. The time now is 12:54 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"