CODEWITHSUNDEEP
excelvba
Carlos80
Carlos80

Reputation: 433

VBA - Variable Worksheet

I have an input sheet in Excel where the use enters a number of account details. Once the form is filled in they press a button that generates an input file using VBA. The first step is that a new worksheet is generated and the name of this sheet in a combination of the clients name and the a hard coded word. As you can imagine this sheet needs to be a variable because this input sheet is being used by a number of people on a number of accounts.

The code I have used to generate the new worksheets is:

ActiveWorkbook.Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = Trim(Sheets("Account Input").Range("B8").Value) & "_ACC"

I'm now trying to save this as a variable so that I can call the variable throughout my code. The syntax I have tried is:

Set WsAcc = Trim(Sheets("Account Input").Range("B8").Value) & "_ACC"

The problem is I get a run-time error 424 object required. I have tried it without the trim and still get an error. Run-time eroor 1004 application-defined or object-defined error.

I know there must be a better way to achieve the desired results, I'm just not too sure.

Thanks in advance.

Upvotes: 1

Views: 70

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Replace:

Set WsAcc = Trim(Sheets("Account Input").Range("B8").Value) & "_ACC"

with:

Set WsAcc = Sheets(Trim(Sheets("Account Input").Range("B8").Value) & "_ACC")

Upvotes: 3

Related Questions