Solved

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

Posted on 2014-11-06
6
162 Views
Last Modified: 2014-11-08
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
0
Comment
Question by:contrain
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40426695
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
 
LVL 30

Expert Comment

by:gowflow
ID: 40428411
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
 

Author Comment

by:contrain
ID: 40429384
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:contrain
ID: 40429390
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
 
LVL 30

Accepted Solution

by:
gowflow earned 500 total points
ID: 40429467
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
 

Author Closing Comment

by:contrain
ID: 40430625
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel Dynamic Chart Title Not Working 21 196
CRM 2011 User Last Name Change due to Marriage 6 55
Modify to work without control shift enter 9 50
EXCEL file checking. 11 48
As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question