Hi,

I have written a code that looks at a specific cell reference in a datasheet.

The lines that contain the specific cell reference are then moved to their own individual worksheet within the same workbook.

The new worksheet is then re-named as the cell reference, however some names exceed the 31 characters allowed to rename a worksheet.

Can you tell me how to resolve the problem

Many thanks

Becky

Hi Rebecca

Thank you for your question

First let me aplologise for the delay in replying to your question. We have had very high volumes of posts and as a result there have been a number of delays in responding questions.

Assuming you haven't already resolved the issue, can I suggest you email me a copy of the workbook to stephenATmicrosofttraining.net and I will consider the problem as a matter of urgency

Regards

Stephen

Good afternoon Stephen,
Many thanks for your help with my code regarding excedding the maimum name length for worksheets. I have used the code you sent me which is naming the worksheets within the characters permitted, however from the "C&1" report isn't coming across, the sheet just remains blank and I can't find the problem with it!!!

Would it be possible to help me anymore on this one??
Many thanks
Becky

Hi Rebecca

Thanks for your question and for the file

I have looked at the problem, and the first thing to state is that you cannot exceed the maximum name length and therefore you need a strategy for naming your sheets. As any company name that exceeds the maximum length will comprise two or more words, I suggest just using the first word of the name as the sheet title. For example "Best Training", would become be titled "Best".

I have modified your AskForSupplier procedure as follows

Sub AskForSupplier()


Dim intLength As Integer
Dim strTitle As String


Call DeleteSheetIfExists


Worksheets.Add after:=Worksheets(Worksheets.Count)

intLength = Application.WorksheetFunction.Find(" ", txtSupplier)

strTitle = Left(txtSupplier, intLength)

ActiveSheet.Name = strTitle
Sheets("C&I").Select

End Sub

The strategy is to use the worksheet function FIND to locate the first space in the supplier name, and then to use this in the VBA function LEFT, to trim off the first name and set it equal to the variable strTitle.

Hope this is useful

If this strategy is not suitable please let me know so we can explore other options

Regards

Stephen