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
contrainAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
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
0
contrainAuthor Commented:
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.
0
wchhCommented:
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

0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Saqib Husain, SyedEngineerCommented:
Then try this code

Sub addextno()
For Each ws In ThisWorkbook.Worksheets
For Each cel In ws.UsedRange
Select Case cel.Value
Case "17 Central": cel.Offset(1, 0) = "'(3129)"
Case "6 North": cel.Offset(1, 0) = "'(4126)"
End Select
Next cel
Next ws
End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wchhCommented:
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

0
Saqib Husain, SyedEngineerCommented:
So, wchh, you like my idea of select case?

At least you could have acknowledged it.
0
wchhCommented:
Sorry, I posted before i see your message...
It is better to trim the text before selection...like cell A26 Apr
0
contrainAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.