How do I update multiple sheets in multiple workbooks in XL 2010

In the attached workbook for Qtr 1 2015, I need to make updates to a sheet for conference rooms. What's listed in red text for jan 1 2015 is the data that needs to be updated for the rest of the Qtr 1 workbook as well as the Qtr 2 - 4 workbooks for next year. Some of the data is new extenions which will have to replace the old extensions, and some of it is new rooms that will need to be added to each day of next year on all four workbooks. The format will be consistent on all sheets, no need to fill in any gaps with multiple blank rows. For the 18 East room, I cannot just find and replace the old extension (4311) with the new one (4319) because 4311 is the new extension for the 20 West room so I don't want to overwrite that, it will probably need to be done with a macro that only recognizes 4311 when it is underneath 18 East.

Thanks
Conference-Rooms-2015-Qtr-1-Copy.xlsx
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.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Copy column A from Jan 15, and then select the Feb and Mar sheets, and then paste into their column A. Then delete any extra rows. Then repeat for the other workbooks.

There are other ways to do it, but given the limited number of sheets you are talking about, that is by far the quickest.

You need to copy from a sheet that has 31 days.
0
gowflowCommented:
Sorry but it is not clear what you need to achieve !!! Can you please explain more clearly.

Do you need a VBA solution or a Formula solution ?

gowflow
0
contrainAuthor Commented:
Hi Gowflow,

I basically want day of each month for  every sheet in the attached workbook to look like the Jan 1 2015 sheet with the same rooms and extensions. What's in red for Jan 1 part of sheet 1 is what needs to be added to the rest of the days of the year not only in the attached workbook but 3 additional workbooks that are laid out the same way.
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).

contrainAuthor Commented:
Hi Philip Burton,

If I just copy column A from the January worksheet, it will copy Jan 2 - 31, and right now only the Jan 1 part of the Jan worksheet has all of the correct data, Jan 2 - Jan 31 do not have the data listed in red that is on the Jan 1 part of the worksheet so I will need to find a way to copy Jan 1 to Jan 2 - 31 before I can copy the whole sheet to the Feb and Mar sheets. I could do the standard copy/paste, but that would entail pasting 30 times on just that one sheet which I am trying to avoid since I also need to do this for the workbooks for the other 3 quarters of the year.
0
gowflowCommented:
Well you need a macro for that and here it is. Put this code in a module in your first workbook that has the red items in Jan1 and run it. It will update the whole workbook with correct conference Rooms.

Then
Copy this same sub into your second workbook and make sure the first day has the updated Conerence room and  change this line
Set WS = Sheets("JAN 15")
to reflect the name of your first sheet in the new workbook and run it.

Do the same for each workbook.

Sub UpdateConfRoom()
Dim WS As Worksheet
Dim MaxRow As Long
Dim I As Long
Dim Rng As Range

Set WS = Sheets("JAN 15")
Set Rng = WS.Range("A2:A41")
Rng.Copy

For Each WS In ActiveWorkbook.Worksheets
    MaxRow = WS.Range("A" & WS.Rows.Count).End(xlUp).Row
    
    For I = 2 To MaxRow Step 41
        WS.Range("A" & I).PasteSpecial xlPasteValues
    Next I
    
Next WS

MsgBox ("Confrence Romm Updated")

End Sub

Open in new window


Let me know if any problem.

PS I ran it on your workbook and here is it attached.
gowflow.
Conference-Rooms-2015-Qtr-1-Copy.xlsm
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
contrainAuthor Commented:
PERFECT! Gowflow gave me the macro I needed and even did my first workbook for me. It ran just as he said it would with the minor adjustment for the first sheet name in each book and it gave me exactly what I wanted. Thank you!!!!
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
Spreadsheets

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.