Thread: How to fill a cell with the name of the worksheet

If I want to fill A1 with "ABC" and A2 with "DEF" AND A3 with the name of the worksheet on multiple worksheets and these actions are not to be done on each worksheet (e.g. not on worksheet 1 & 2 but do it only on worksheet 3-20 or more), how can I do it with VBA?

With reference to the post on http://www.excelforum.com/excel-new-...ary-sheet.html, I came up with the following codes. Could anyone advise? Many thanks.

Option Explicit
Sub AddInfo()
Dim ws As Worksheet
Dim MySh As Variant

MySh = "Master DB"

For Each ws In Worksheets
If ws.Name <> MySh Then _

With ws
      .Range("A1").value = "ABC"
      .Range("A2").value = "DEF"
End With

Next ws

End Sub

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Try this

Option Explicit

Sub AddInfo()
    Dim n As Long
    Dim MySh As String

    MySh = "Master DB"

    For n = 3 To Sheets.Count
        If Sheets(n).Name <> MySh Then
            With Sheets(n)
                .Range("A1") = "ABC"
                .Range("A2") = "DEF"
                .Range("A3") = Sheets(n).Name
            End With
        End If
    Next

End Sub

Hope this helps

Hi,

You can of course also return the sheet name with a formula. First group all the sheets in which you want the sheet name to appear, i.e. sheets 3-20

Enter "ABC" in A1 on any sheet, "DEF" in A2 on any sheet and
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,31)
in A3 on any sheet.

Then undo the grouping.

Regards

Thanks Marcol ! That worked very well. I would like to add one more cell A4 and it's value changes every time, let say, it's the date. I tried to add an InputBox after Range("A3")... but when I ran the Marco, the InputBox prompted up for every worksheet (I have more than 30 worksheets @@). I'd also like to do some formatting and adjust the column width. Could you take a look at below codes and let me know if that's the way?

And sorry, one more, what if I don't want the actions done on a few worksheets. They might not be the first two worksheets. Sometimes it might be the 21st and 29th while sometimes the others. I won't be able to tell the sequence of the worksheets because it might change every time (unless I count) but what I could be certain was the name of the worksheet.

Option Explicit

Sub AddInfo()
    Dim n As Long
    Dim MySh As String

    MySh = "Master DB"

    For n = 3 To Sheets.Count
        If Sheets(n).Name <> MySh Then
            With Sheets(n)
                .Range("A1") = "ABC"
                .Range("A2") = "DEF"
                .Range("A3") = Sheets(n).Name
                .Range("A4") = InputBox("Please enter the date.") 
                    With Range("A1:A4")
                         .Interior.ColorIndex = 35
                         .font.bold = True
                         .size = 14
                    End With
                 Columns("A:A").ColumnWidth = 18
            End With
          
        End If
    Next

End Sub

Thanks Richard. That's what I did at the first place but it didn't work somehow. I did the actions recording it as Marco but when I played it afterwards, the actions were performed on only the worksheet which was activated.

Hi,

Try doing it manually and not bothering to record a macro.

Copying the cells then Grouping the sheets and pasting works OK for me.

Regards