CODEWITHSUNDEEP
vbaexcel
Robert Sorgedrager
Robert Sorgedrager

Reputation: 23

How do I use the result of an input box in another bit of my code?

So I am very new to VBA and coding and have been playing around writing very simple subs. The sub takes a sheet from a workbook, copies into a new workbook and then saves it. the problem is that I want to be able to specify the name it uses when it saves the document. I am using an input box, I just can't seem to get the code to accept the result. This is my code

Sub CC_Export()
Dim name As String
Dim Wb As Workbook

Set Wb = Workbooks("workbook1")
    Wb.Sheets("Sheet1").Copy
    name = InputBox("Enter Name of New Workbook", "New Workbook")
    ActiveWorkbook.SaveAs "name.xlsx", FileFormat:=51

End Sub

Upvotes: 2

Views: 63

Answers (3)

user3598756
user3598756

Reputation: 29421

edited to take advantage of implicit extension assumptions as per @Pawel Sotor answer and further reduce the code length

Rory gave you the solution

Just to throw in you can shorten down your code as follows:

Sub CC_Export()
    Workbooks("workbook1").Sheets("Sheet1").Copy
    ActiveWorkbook.SaveAs InputBox("Enter Name of New Workbook", "New Workbook"), FileFormat:=51
End Sub

Upvotes: 3

PSotor
PSotor

Reputation: 346

Just replace the "name.xlsx" with name, so your code should look like this: ActiveWorkbook.SaveAs name, FileFormat:=51

Upvotes: 2

Rory
Rory

Reputation: 34045

Your code is using the literal text "name.xlsx". To use the variable you need to concatenate its value into your text string

ActiveWorkbook.SaveAs name & ".xlsx", FileFormat:=51

Upvotes: 4

Related Questions