?
Solved

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

Posted on 2014-11-06
6
Medium Priority
?
168 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 31

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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

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 31

Accepted Solution

by:
gowflow earned 2000 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

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

As freelancing is becoming more and more common in the tech industry, certain obstacles are proving to be a challenge to those who are used to more traditional, structured employment. This article is meant to help identify such obstacles and offer a…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

762 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