• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Open Existing and Save As through currently Open Document

jsabin

New Member
Hoping you guys can help. I have an existing Open document where I will assign a macro to a button. I want that macro to open another file and save that file to a new name. Here's the code I have so far but it doesn't seem to work.

Code:
Sub Test()
  Workbooks.Open Filename:=ThisWorkbook.Path & "\TEMPLATE.xlsm"
  Dim varCellvalue As String
  varCellvalue = Range("AM12").Value
  Windows("TEMPLATE.xlsm").Activate
  ActiveWorkbook.SaveAs = ThisWorkbook.Path & "\SAVED PROJECTS\" & varCellvalue & ".xlsm"
End Sub
! Use CODE TAGS !
 
Last edited by a moderator:

jsabin

New Member
I'm Getting a compile Error: "Expected Function or Variable" which highlights the ".SaveAs =" in the code.
 

Marc L

Excel Ninja

So just reading the VBA inner help for SaveAs method
you must see the equal sign is not necessary ! …​
 

jsabin

New Member
That helps. I think I'm getting closer. What I'm getting now is a "Method 'Save As' of "object'_Workbook' failed" error. The Debugger takes me to this line.

[ActiveWorkbook.SaveAs ThisWorkbook.Path & "\SAVED PROJECTS\" & varCellvalue & ".xlsm"]
 

Marc L

Excel Ninja
Check :

¤ if each folder already exists

¤ what contains the cell / variable used in the path ?
 

jsabin

New Member
As a test, I changed the code to this below and it worked flawlessly. The problem is the name of the file does need to be dynamic, based on user input.

[Sub Test()
Workbooks.Open Filename:=ThisWorkbook.Path & "\DESIGN-PAD\DESIGN-PAD.xlsm"
Windows("DESIGN-PAD.xlsm").Activate
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\DESIGN-PAD\SAVED PROJECTS\TEST.xlsm"
End Sub]
 

jsabin

New Member
Update:
I found the problem. Now I just need to figure out how to fix it. The code is referencing cell AM12 of the file that the code is opening and saving. It needs to reference AM12 on the file that contains the macro which is titled "USER INTERFACE" on sheet also titled "USER INTERFACE"
 

jsabin

New Member
Thank you very much Marc for your help! Here is the end code if you're interested.
Thanks again!

[Sub Test()
Workbooks.Open Filename:=ThisWorkbook.Path & "\DESIGN-PAD\DESIGN-PAD.xlsm"
Dim varCellvalue As String
varCellvalue = Workbooks("USER INTERFACE.xlsm").Sheets("USER INTERFACE").Range("AM12").Value
Windows("DESIGN-PAD.xlsm").Activate
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\DESIGN-PAD\SAVED PROJECTS\" & varCellvalue & ".xlsm"
End Sub]
 

jsabin

New Member
on a side note, I just noticed the moderator edit requesting to use code tags. I thought that's what I was doing by enclosing the code with []. Do you know what I'm doing wrong?
 
Top