Solved

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

Posted on 2014-11-06
6
157 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
  • 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 29

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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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 29

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…

856 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