none
rename sheets name with VBA RRS feed

  • 質問

  • I am a beginner in VBA. I got an urgent problem with VBA. My teacher asked me to make a program to rename the sheet's name.  As his request, the program can rename the sheet's name as a date constantly and automatically. For instance, as he said, the sheet1 can be named as 2020/11, and then I can set a command button, which the next sheet (2) named 2020/12 can pop out as I click it. Moreover, the sheet(3) name will be 2021/01, if I click the command button again. I tried my best to tackle this problem, but my work still is ticking over.  At least seems to me, it is an insurmountable barrier between further studying and quit.   Please help me, if you have time to do this. Thank you a lot in advance 
    2020年11月5日 8:21

すべての返信

  • If he teaches you VBA, I think you should rely on him instead of this forums.

    For instance, as he said, the sheet1 can be named as 2020/11, 

    I doubt it. Unfortunately, Excel does not allow its sheet names to contains slash (/).
    But You can name it "2020-11" or "Nov.2020" instead of "2020/11".

    ' NG
    ' Sheet1.Name = "2020/11"
    
    ' OK
    Sheet1.Name = "2020-11"
    Sheet2.Name = "2020-12"
    Sheet3.Name = "2021-01"
    
    and then I can set a command button, which the next sheet (2) named 2020/12 can pop out as I click it. Moreover, the sheet(3) name will be 2021/01,
    Option Explicit
    '
    ' This workbook contains only SINGLE worksheet, and the worksheet name is "2020-11".
    ' If your book contains tri-sheets, let's try to write the other code yourself.
    '
    Private Sub CommandButton1_Click()
        Dim firstSheet As Excel.Worksheet
        Set firstSheet = ThisWorkbook.Worksheets(1)
        Dim lastSheet As Excel.Worksheet
        Set lastSheet = ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
    
        Dim firstMonth As Date
        firstMonth = CDate(firstSheet.Name & "-01")
    
        Dim nextMonth As Date
        nextMonth = DateAdd("m", ThisWorkbook.Worksheets.Count, firstMonth)
    
        Dim newSheet As Excel.Worksheet
        Set newSheet = ThisWorkbook.Worksheets.Add(After:=lastSheet)
        newSheet.Name = Format(nextMonth, "yyyy\-MM")
    End Sub

    2020年11月5日 13:18
  • (日本語じゃダメなんだろうか…)
    2020年11月5日 22:58