• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vba code rename the sheet

J

Jagdev Singh

Active Member
Hi Experts

I have a code which adds a new sheet in a workbook performs the neccesary action and later I move it and save it in a default folder. I want to rename this moved sheet with the cell value which is in row1. I merged the rows (A1:M1) and the name to be renamed is the words placed here. I tried many permutation and combination, but failed to complete this action.

Code: 
Sheets("Temp").Select
    Sheets("Temp").Move
    Sheets("Temp").Name = "Score Result"
ActiveWorkbook.SaveAs Filename:="C:\Users\Sinderjt\Desktop\New folder (12)\Score Sheet.xlsx"
   
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Users\Sinderjt\Desktop\New folder (12)\Score Sheet.xlsx\Score" & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=False, _
    IgnorePrintAreas:=True, OpenAfterPublish:=False

Regards,
JD
 
Hi,

Try this. Assuming that in Sheet2, Range A1 has the name of the sheet. (A1 to M1 is merged as you said)

Code: 
Dim wsNew As Worksheet
new_sheet_name = Sheet2.Range("A1").Value
Sheets.Add after:=Sheets(Sheets.Count)
Sheets(ActiveSheet.Name).Name = new_sheet_name
 
Hi Sn

I do not want to add any sheet in the sheet which I am moving from my workbook after completing my required task
eg "Temp".

I want to rename this sheet with the value avilable in merge range (A1:M1) in the temp sheet.

Regards,
JD
 
I have looked at your merged cells in VBA and even the great and the good say as I do below.


Avoid merging cells


Merged cells can help you arrange values in a meaningful way, but they come with problems -- numerous problems, big problems.


For instance, Excel won't apply column formats to a merged cell unless you select all the columns that comprise the merge.


In addition, not all cell formats, stick once you emerge a cell.


You can't sort a column with merged cells.


You can't even select a single-column range if there's a merged cell in it -- go ahead, try!, the whole column will become merged, not good.


You cannot put a filter on it. The problem is the filter is completely useless because the filter will groan with the "merged cells need to be identically sized." Warning, which in English means you have to make each group of merged cells the same size as the largest group. And you have to find them all!


Merging cells in columns and rows could lead to data loss, bad thing.


Formulas and Functions that refer to merged cells will not work, bad thing.


Don't hesitate to use merged cells if you really need them (you don’t), but they will limit what you can do to the cells and even the columns involved.


Center Across Selection is a far better alternative to merging.


To apply this format, select the cells you want to appear merged and then launch the Alignment group dialog, Ctrl + 1, and click the Alignment tab. Center Across Selection is in the Horizontal drop-down.


You will get the desired look you want but without the merged cell's problems.



Although I put this together for "normal Excel" it still goes the same for VBA,

DO NOT USE MERGED CELLS.


.
 
Hi Bobhc

Thanks for the explanation, but it is the requirement which I have to consider. I managed to get it workable with making few changes in the below code. Posting it here.

Code: 
ActiveWorkbook.SaveAs Filename:="C:\Users\Sinderjt\Desktop\New folder (10)\" & [A1]  & ".xlsx"

Regards,
JD
 
You must log in or register to reply here.
Back
Top