Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 179
  • Last Modified:

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
0
contrain
Asked:
contrain
  • 3
  • 2
1 Solution
 
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
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!

 
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
 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now