Macro to Write Formula into Specific Cell, and then Paste Values

[bkanne]

Hi,

If possible, could someone please help me write the following macro?

I need to make Cell B3 in the active (or preferably all selected sheets) to:

1) Drop in the following formula: =+MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

2) Then calculate the spreadsheet,

3) and then paste values (just for those specific cells).

This formula will make cell B3 in each selected worksheet equal to the name of each individual worksheet.

The reason I don't leave this formula in place is that it creates an external link to itself, which I would like to remove.

Thank you so much!
Ben

 

[Derek McGill]

Can you post a file please.

 

[Monty]

Hello bkanne

Welcome to forum.

Always upload your sample file for a quick solution from the members..otherwise need to guess which can be incomplete solution.

thanks
Monty

 

[bkanne]

I typically do that, but in this case, there is no sample file to upload - I would just be uploading a blank spreadsheet. This is a generic question that applies to literally any spreadsheet.

 

[Derek McGill]

Try this :-

Sub TabNameToB3()
Dim SheetNames As String
       
    SheetNames = ActiveSheet.Name

    For J = 1 To ActiveWorkbook.Sheets.Count
        Sheets(J).Cells(3, 2).Value = Sheets(J).Name
    Next
End Sub