Need help naming a worksheet based off another worksheet in VBA

[codys21]

Right now I have two sheets, named Sept 2014 Raw Data and Sept 2013 Raw Data. I am taking the raw data and filtering it using VBA and putting the filtered data into a new worksheet. What I would like to be able to do is name the new worksheet based on where the data came from. For instance, if I ran my code on the Sept 2014 Raw Data worksheet, I would like my new sheet to be named Sept 2014, likewise for the Sept 2013 Raw Data. So, if there's some sort of code which can name the sheet from the first x amount of characters (in this case 9) that would be fantastic.

Thanks for the help!
Cody

 

[mrivera94]

Try using the left function.

 

[bbott]

Something like this:

Sub SheetName()
    
    Sheets.Add.Name = Left(ActiveSheet.Name, 9)
    
End Sub

 

[codys21]

Try using the left function.

I'll give it a shot, thanks. I am not very experienced with VBA so let's hope this works correctly.

 

[codys21]

Something like this:

Sub SheetName()
    
    Sheets.Add.Name = Left(ActiveSheet.Name, 9)
    
End Sub

This doesn't work because I need to use the left 9 characters of the sheet to the right of the active sheet. So I need to reference the raw data sheet and use the left 9 characters of that to name the newly added active sheet.

 

[bbott]

This doesn't work because I need to use the left 9 characters of the sheet to the right of the active sheet. So I need to reference the raw data sheet and use the left 9 characters of that to name the newly added active sheet.

This would need to be done before you add the sheet. Try running just my code while you have the Sept 2014 Raw Data sheet active.

 

[bbott]

This doesn't work because I need to use the left 9 characters of the sheet to the right of the active sheet. So I need to reference the raw data sheet and use the left 9 characters of that to name the newly added active sheet.

If you really want it to use the sheet to the right of the active sheet, then:

Sheets.Add.Name = Left(Sheets(ActiveSheet.Index + 1).Name, 9)

 

[codys21]

This would need to be done before you add the sheet. Try running just my code while you have the Sept 2014 Raw Data sheet active.

It still doesn't work because it is adding a new sheet and then trying to name it based off of this new sheet. So, it's taking "sheet3" and trying to get the 9 leftmost characters and that's not working.

 

[codys21]

If you really want it to use the sheet to the right of the active sheet, then:

Sheets.Add.Name = Left(Sheets(ActiveSheet.Index + 1).Name, 9)

Even with that I am getting the error "That name is already taken. Try a different one."

 

[bbott]

It still doesn't work because it is adding a new sheet and then trying to name it based off of this new sheet. So, it's taking "sheet3" and trying to get the 9 leftmost characters and that's not working.

Why are you running the code on Sheet3? Of course it won't work on Sheet3 since the left 9 (or in this case 6) characters are Sheet3, which already exists.

As I said in my previous post, try it on Sept 2014 Raw Data. The newly added sheet doesn't become the active sheet until the full line of code is executed. Therefore, Sept 2014 Raw Data is the active sheet prior to adding and naming the new sheet.

This code works on my machine.....