Hi Daniel. May try: Sheet1.Copy After:=Sheet2 ActiveSheet.Name = "New Name" Remark: 1. The copied sheet can be placed "before" or "after" a specific sheet, for example, Sheet2. 2. Ignore the variable "Before"/"After" if want to copy the specific sheet as a new workbook, for example Sheet1.Copy. Hope this helps. Thanks for watching and have a nice day.
What code should I use if I want to name a new sheet = to the value of a cell with a formula in it. For example, I want to use the value in “A5” as the name of the new sheet and “A5” has a formula =M1&R1, both cells are merged cells as well (M1&M2; R1&R2).
Hi Robert. May try 1. sheet.Name = Sheet2.Cells(1, "M") & " " & Sheet2.Cells(1, "R") 'this is with space 2. sheet.Name = Sheet2.Cells(1, "M") & Sheet2.Cells(1, "R") 'this is without space For merged cells, Excel takes the left most cell by default, eg. M1&M2 returns M1. Hope this helps. Thanks for watching and have a nice day.
Thank you for the quick reply. The formula worked however the copied sheet does not refer to a 3rd sheet where the original sheet derive it’s values from. Example, Sheet “A” (original Cell )“A1” =A1 in sheet B but the copied sheet (sheet C) refers to Sheet A “A1” instead of sheet AB’s A1.
My pleasure. Robert, regarding your issue, try to amend the sheet number accordingly. "Sheet2" that I suggested is just for illustration purpose since I was using Sheet2 at the time I record this video. Besides, the flow is very important. If you wish to use the value(s) in the new sheet as name, kindly make sure all required values are transferred/pasted to the new sheet before performing cell referencing. As an illustration, ①transfer/combine/copy values to the new sheet 👉 ②assign name using cells in the new sheet Hope it helps. Have a nice day.
Hello Yan Ling. I guess it is due to duplication since Excel couldn't have sheets with the same name. May add the code "On Error Resume Next" before adding a new sheet, see if it is working fine and correctly. 🤔 Or maybe, may I have a look on your code? Thanks for watching and have a nice day.
@@ahsingtv Alright thank you! I actually would like to create a new sheet with the same name. For example, for sheet name "Straw", then the following sheets would be "Straw (2)", "Straw (3)", "Straw (4)" and so on. However what excel shows now is "Straw", then prompts the error name taken, and the next sheet added is named "Sheet 1", "Sheet 2"... Do you by any chance know a way to solve this? Sorry for not explaining better previously. Thank you so much!!!
Hi Yan Ling, may try 👇 see if it works. For Each ws In ActiveWorkbook.Worksheets If Left(ws.Name,5)="Straw" then StrawCount=StrawCount+1 End If 'take note that "ws" is a dummy used to represent a selected worksheet 'then continue to rename the new sheet sheet.Name="Straw (" & StrawCount +1 & ")" Yan Ling, amendments might be needed to fulfil the necessary requirements since I'm not sure what is/are your actual condition(s). Hope it helps.
you are a great teacher!
Thanks!
Thanks for watching and have a nice day.
Hi Ah sing's
What code should i use if instead add a new sheet i want copy a specific sheet and rename it?
Thanks,
Hi Daniel. May try:
Sheet1.Copy After:=Sheet2
ActiveSheet.Name = "New Name"
Remark:
1. The copied sheet can be placed "before" or "after" a specific sheet, for example, Sheet2.
2. Ignore the variable "Before"/"After" if want to copy the specific sheet as a new workbook, for example Sheet1.Copy.
Hope this helps. Thanks for watching and have a nice day.
What code should I use if I want to name a new sheet = to the value of a cell with a formula in it. For example, I want to use the value in “A5” as the name of the new sheet and “A5” has a formula =M1&R1, both cells are merged cells as well (M1&M2; R1&R2).
Hi Robert. May try
1. sheet.Name = Sheet2.Cells(1, "M") & " " & Sheet2.Cells(1, "R") 'this is with space
2. sheet.Name = Sheet2.Cells(1, "M") & Sheet2.Cells(1, "R") 'this is without space
For merged cells, Excel takes the left most cell by default, eg. M1&M2 returns M1.
Hope this helps.
Thanks for watching and have a nice day.
Thank you for the quick reply. The formula worked however the copied sheet does not refer to a 3rd sheet where the original sheet derive it’s values from. Example, Sheet “A” (original Cell )“A1” =A1 in sheet B but the copied sheet (sheet C) refers to Sheet A “A1” instead of sheet AB’s A1.
My pleasure.
Robert, regarding your issue, try to amend the sheet number accordingly. "Sheet2" that I suggested is just for illustration purpose since I was using Sheet2 at the time I record this video.
Besides, the flow is very important. If you wish to use the value(s) in the new sheet as name, kindly make sure all required values are transferred/pasted to the new sheet before performing cell referencing.
As an illustration, ①transfer/combine/copy values to the new sheet 👉 ②assign name using cells in the new sheet
Hope it helps. Have a nice day.
Hello Ah Sing, what if I want to create a new sheet which is running off the same macro button? It shows error as the name is taken. Thank you!
Hello Yan Ling. I guess it is due to duplication since Excel couldn't have sheets with the same name. May add the code "On Error Resume Next" before adding a new sheet, see if it is working fine and correctly. 🤔 Or maybe, may I have a look on your code?
Thanks for watching and have a nice day.
@@ahsingtv Alright thank you! I actually would like to create a new sheet with the same name. For example, for sheet name "Straw", then the following sheets would be "Straw (2)", "Straw (3)", "Straw (4)" and so on. However what excel shows now is "Straw", then prompts the error name taken, and the next sheet added is named "Sheet 1", "Sheet 2"... Do you by any chance know a way to solve this? Sorry for not explaining better previously. Thank you so much!!!
Hi Yan Ling, may try 👇 see if it works.
For Each ws In ActiveWorkbook.Worksheets
If Left(ws.Name,5)="Straw" then
StrawCount=StrawCount+1
End If
'take note that "ws" is a dummy used to represent a selected worksheet
'then continue to rename the new sheet
sheet.Name="Straw (" & StrawCount +1 & ")"
Yan Ling, amendments might be needed to fulfil the necessary requirements since I'm not sure what is/are your actual condition(s).
Hope it helps.
@ahsingtv okay thank you so much! I'll try and see if it works 👌🏻
My pleasure. Have a nice day 😃