Forum Discussion

Ian_A_Brown's avatar
Ian_A_Brown
Copper Contributor
Jul 15, 2021

Macro uses info from sheet it was created in, NOT the current sheet

Hi folks -

frustrating problem here. I have a workbook with over 100 sheets. I want to name each sheet with a school name in the contents in A1 of of that sheet, but first I have to un-merge A1 and separate school names from other text in the cell.

 

I've tried cutting/pasting school name into a separate cell.

I've tried deleting all info BUT the school name in A1 (so no cut and paste, only deleting text)

 

Either way, when I apply the macro to a different sheet, the school name in the original sheet I recorded the macro in shows up every time I run the macro.

 

Appreciate all the help I can get!

excel
Macros and VBA

2 Replies

    • Ian_A_Brown's avatar
      Ian_A_Brown
      Copper Contributor
      Jul 19, 2021
      thanks for responding - I figured out the above problem, but now I have an issue where I want to go through every worksheet in a workbook and rename every sheet. Every building I reference has two sheets, so when a duplicate sheet name causes an error, I set a subroutine to create a concatenation of the first sheet name. However, the routine stops after the first two renaming episodes:

      Sub Set_Up_Sheet_Name()

      ' Set_Up_Sheet_Name Macro
      '
      Dim x As Integer
      x = Application.Worksheets.Count
      'now cycle through all the worksheets
      For i = 1 To x
      Worksheets(i).Activate
      ActiveSheet.Range("A1:E1").Select
      Selection.UnMerge
      ActiveSheet.Range("A1").Select
      With Selection
      .UnMerge
      .Range("A1").Copy Range("F1")
      'copies contents of A1 to F1 for posterity, and wraps text
      .Replace What:= _
      "(replace a text string here not needed for sheet name)", Replacement:=""
      .Replace What:= _
      "(replace a text string here not needed for sheet name)", Replacement:=""
      'gets rid of all text except facility name and dates
      .Replace What:= _
      Chr(10), Replacement:=""
      .Replace What:= _
      "(text string not needed for sheet name)", Replacement:=""
      .Replace What:= _
      " --", Replacement:=""
      .WrapText = True

      End With

      Set target = Range("A1")
      ActiveSheet.Name = Left(target, 26) & (" Data")


      If Err Then
      If Err.Number = 1004 Then
      Err.Clear
      GoTo DuplicateName
      End If
      End If


      DuplicateName:
      Set target = Range("A1")
      ActiveSheet.Name = Left(target, 31)

      Next i

      End Sub

Resources