asked on

Need macro to add phone extensions to sheets

In Excel 2003, have workbooks for conference room reservations, one workbook per quarter containing 3 months worth of reservations per book (see attached). I need to add the phone extensions for each room, but two new rooms have been added to every sheet without their extensions so I will need a macro to add the following extensions in the cell below each conference room, so for room 17 Central I will need (3129) listed in each cell below 17 Central (it needs to include the parentheses too; first occurance is in cell A31 on the April sheet).
And the second one is (4126) under each occurance of 6 North; first occurance is in cell A34. I'll need to add this to all 3 sheets for each Quarterly workbook.
Conference-Rooms-2011.XLS

Saqib Husain

For your case you do not really need a macro.

Select all the sheets. (Select the first, and then press shift while selecting the last one)

Now make the change to any one sheet and it will be done to all the sheets.

Saqib

contrain

ASKER

Unfortunately, not all the sheets line up the same way, so what would be entered on the April sheet in cell A31, in the May sheet I'd need it in A34, and what would be entered in A34 in the April sheet, would need to be entered in A37 in the May sheet.

wchh

Please refer to the Macro below:-

Sub Insert()
   Dim Worksheet As Worksheet
   Dim i As Integer
   For Each Worksheet In ThisWorkbook.Worksheets
       With Worksheet
       For i = 2 To .Cells(Worksheet.Rows.Count, "A").End(xlUp).Row
           If Trim(.Cells(i, 1).Value) = "17 North" Then
            .Cells(i + 4, 1).Value = "17 Central"
            .Cells(i + 5, 1).Value = "'(3129)"  '<-- added
           End If
       Next i
       End With
    Next Worksheet
            
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Saqib Husain

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

wchh

Or Macro below

Sub InsertExt()
   Dim Worksheet As Worksheet
   Dim i As Integer
   For Each Worksheet In ThisWorkbook.Worksheets
       With Worksheet
       For i = 2 To .Cells(Worksheet.Rows.Count, "A").End(xlUp).Row
           Select Case Trim(.Cells(i, 1).Value)
           Case "17 Central"
              .Cells(i + 1, 1).Value = "'(3129)"
              Case "6 North"
              .Cells(i + 1, 1).Value = "'(4126)"
           End Select
       Next i
       End With
    Next Worksheet
            
End Sub

Open in new window

Saqib Husain

So, wchh, you like my idea of select case?

At least you could have acknowledged it.

wchh

Sorry, I posted before i see your message...
It is better to trim the text before selection...like cell A26 Apr

contrain

ASKER

This macro works exactly as I need it to, and it filled in the information for both rooms as I requested. And since it was the first totally correct answer I received, I am awarding the points to this Expert. I was able to run the macro one time and update the entire workbook perfectly.