VBA code for default worksheets name

[Faridwahidi]

Hi,

Does anyone how to create default worksheets name?

I have tried Worksheets("*_1").Select or Worksheets("=*_1").Select both error

What I want is user can create new worksheets with any name and ending with _1,_2,_3 and so on.

e.g. Worksheets("David_1").Select or Worksheets("Roams_2").Select


Regards,
Farid.

[patel45]

Activate the macro recorder, add a new sheet, stop recorder, then you have the code

[Faridwahidi]

Hi patel45,

It is not such easy way as you mentioned.

when I said "user" it means that the people who will only run or use macro, not creating or modifying the code.

Assume that there is 10 existing worksheets in the workbooks. When there is new worksheets required to be created, the administrator will add new one but it needs defaulted sheetname so that macro can recognize the sheets created in order to copy and paste information into Workbooks summary.

Regards,
Farid.

[HaHoBe]

Hi, Farid,

you would need to explain why you think to need the name of any new entered worksheet and at which times. If any new worksheet is inserted this is the active sheet by default.

Please Login or Register  to view this content.

And this is one of the very few times that any macro recorder code will not shed any light on what you want to do as everything in the code would be hard-coded.

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

Ciao,
Holger

[Faridwahidi]

hi HaHobe,

Please refer to my 2nd post.

Thanks,
Farid

[greggpetersen75]

So are you saying that the last 2 digits of each Worksheet will be _1, _2 etc and you are trying to select it based on those two digits alone regardless of the preceding text?

[Faridwahidi]

Hi greggpetersen75,

Yes, that is what I want.

[HaHoBe]

Hi, Farid,

sorry but I canīt find any information for coding in that post. Maybe a loop over all worksheets excluding the summary would be good enough to solve the problem.

...that macro can recognize the sheets created in order to copy and paste information into Workbooks summary.

Maybe you should have a look at the codes provided?

Ciao,
Holger

[greggpetersen75]

Holger's code goes through the name of each worksheet. And the worksheet ending in 3 will become "ws". Then from there, you can just do ws.Select.

[HaHoBe]

Hi, Farid,

check for an underscore before running

Please Login or Register  to view this content.

Ciao,
Holger

[Faridwahidi]

Hi HaHobe,

Thanks.