1

Background: I'm working with a macro that transfers data between different workbooks. One of the workbooks is a conduit for data and doesn't change. The other workbook is a blank form that is populated, saved as a specific name and then reused.

Problem: The workbook being saved needs to use the contents of a cell in the conduit workbook for it's name.

I found a similar post here. The answer gives me a syntax error.

Sub TestTestTestTest()
'
' TestTestTestTest Macro
'

'
    Dim name As String
    name = Cells("Contents", A1)
    ActiveWorkbook.SaveAs() Filename:=name**********
End Sub

The starred line is the source of the syntax error.

Community
  • 1
  • 1
bdkong
  • 181
  • 3
  • 9
  • 22

2 Answers2

1

Have you considered the file path? or which workbook you are getting A1 from?

I assume Contents is a sheet and A1 is the filename ( for future people looking at this Contents was not the sheet)

Dim name As String
name = range("A1").value
ActiveWorkbook.SaveAs Filename:=name

just for your knowledge to do the same with cells you can use

cells(1,1).value

Cells expects numerical values for column/row

To point to a sheet you can use

sheets("sheet1").range("A1").value
sheets("sheet1").cells(1,1).value

You could shorten this whole thing by using

ActiveWorkbook.SaveAs Filename:=range("A1").value
99moorem
  • 1,955
  • 1
  • 15
  • 27
  • Cell("Contents",A1) Returns the contents of cell A1, not my sheet name. – bdkong Jul 15 '15 at 15:02
  • 1
    cells will never work like that. See https://msdn.microsoft.com/en-us/library/office/ff194567.aspx – 99moorem Jul 15 '15 at 15:11
  • Ahhh, was looking at the formula, not the VBA code https://support.office.com/en-ca/article/CELL-function-51bd39a5-f338-4dbe-a33f-955d67c2b2cf – bdkong Jul 15 '15 at 15:17
  • 1
    ahhh. range("A1").value without the sheet ref will give you the contents of A1 on the activesheet. I have amended the above answer slightly – 99moorem Jul 15 '15 at 15:19
  • Is there any way to use two cells to name the file? For example the contents of A1 underscore contents of A2? – bdkong Jul 15 '15 at 15:29
  • 1
    name = range("A1").value & "_" & range("A2").value you can concatenate as much as you like using &. – 99moorem Jul 15 '15 at 15:31
  • Thank you so much! I regret that I have but one upvote to give. – bdkong Jul 15 '15 at 15:37
1

You have to remove brackets:

ActiveWorkbook.SaveAs Filename:=name

Or if you want to use bracket, you need to put parameters inside them and add keyword Call at the beginning:

Call ActiveWorkbook.SaveAs(Filename:=name)
mielk
  • 3,890
  • 12
  • 19