Open Existing and Save As through currently Open Document

[jsabin]

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.

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 !​

 

[Marc L]

but it doesn't seem to work.

So what seems the issue ?
​

 

[jsabin]

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

 

[Marc L]

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

 

[jsabin]

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]

Check :

¤ if each folder already exists

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

 

[jsabin]

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]

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"

 

[Marc L]

Like Range("'USER INTERFACE'!AM12").Text …​

 

[jsabin]

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]

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?

 

[Marc L]

As it's permanently written in the Tip frame below
or just use the code icon …​