Changing Worksheet names based on cell value with VBA

[cody.starr]

I have a workbook made up with all my sheets ready for data. I want a VBA script that will rename the worksheet as soon as i change the cell value.

I am using cell A1 to condense two cells into one name using this formula:
=IF(OR(K2="",N2=""),"",CONCATENATE(N2," ",K2))

So i want to run a worksheet rename based off A1.
I have given generic names to the worksheet so they are place holders, i do not need to create a new sheet. And i also want the script to only be able to be applied to the 20 specific sheets. (27 total sheets in workbook)

Can anyone help? My VBA skills are nill

Thanks in advance.

[davesexcel]

Check out a sample
https://www.youtube.com/watch?v=HsB3Wz8B9Sg

[cody.starr]

thanks but i followed step by step and got nothing. I am using a formula in cell A1 to create text could that be causing issues? also will i have to do this for each of the worksheets that i want the macro to work with? If not, how do i discriminate the pages i dont want the macro to run on?

[davesexcel]

Ah yes, you would have to use sheet calculate, not sheet change.

How many sheets would you be dealing with?

[cody.starr]

i have 27 sheets total

20 of them are the ones i want the macro to run on.
they are ID'd in the VBA editor as sheet 2,5,10-27

[davesexcel]

Possibly in the workbook module, for example you and add more sheets that you don't want to include,

Please Login or Register  to view this content.

[cody.starr]

Im getting nothing, no change in name and not even an error message

[Logit]

.
Place this code in the ThisWorkbook module:

Please Login or Register  to view this content.

[cody.starr]

Thanks this worked!!! you guys are great

[Logit]

Glad to help

[Logit]

Thanks for the Add Rep

[tragatz1]

Hello, and thank you in advance for any advice. I have 22 queries to export to Excel from Access 2010. [B]DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "NAME_01", "\\corp\data\fac_gen\6C45S - STOCK PARTS CRIB\Open PO's Data\FINAL_PRODUCT_FOR_MAIL\OPEN_POs_PROJECT.XLSX", -1, "NAME_01".
Rather than have the query export as "Name_01", I would like to have it change dynamically to match the worksheet in the same workbook named "Planners", and to match B2. Name_02, would be the same, only B3 and so on through B23. Name_01=B2, Name_02=B3, Name_03=B4 and so on. I have tried :

Dim xl As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Set xl = New Excel.Application
Set xlBook = xl.Workbooks.Open("\\corp\data\fac_gen\6C45S - STOCK PARTS CRIB\Open PO's Data\FINAL_PRODUCT_FOR_MAIL\OPEN_POs_PROJECT.XLSX")

Set xlSheet = xlBook.Worksheets(4)



xlSheet.Name = "test"
'test line above I tried to enter a path, but no luck.*******************************************************************************

xl.DisplayAlerts = False
xlBook.Save
xlBook.Close True
xl.Quit
xl.DisplayAlerts = True

Set xlSheet = Nothing
Set xlBook = Nothing
Set xl = Nothing

I am using a kill command in Access to delete the spreadsheet prior to rebuilding it with Access each day, so I can't use macros within Excel and they would be deleted every day. Thanks!

[Arkadi]

tragatz1,

Welcome to the forum, however:

You need to start your own thread if you want help, jumping on an old one from a year and a half ago, and not even related in subject (which would not really make a difference), won't get you any help. Sorry, not trying to be difficult, but it just doesn't work that way. Also, please check the forum rules, you need to use code tags when posting code.