Update sheets frm master

[Cpinhey]

I have a master 'all office' worksheet where all data for all offices is entered, there are 15 offices that each have a sheet containing the same data pertaining to the individual office as that on the 'all office' sheet.

Up until now I've been using a series of VLookups to update the individual office sheets. But, it is now necessary to start adding new records to the the master 'all office' spreadsheet. Is there an easy way to ensure that whatever data is entered on the 'all office' sheet is copied to the appropriate office sheet. There is a column for 'office' on all sheets.

For example, I enter a new row of data for office AA on the 'all office' master sheet. I would like the sheet for office AA to automatically update with the new additional data from the master 'all office' sheet.

Any suggestions? This can be done manually, but I figured there's no harm in asking if there is a better way.

 

[Sthrncali]

This can easily be accomplished with a macro...

What does your data table look like?

I am assuming you keep all data on the master at all times, meaning if you removed a line from the master you would also want it removed from the sub sheet at the same time?

Are the subsheet names the Locations?

i.e.

OfficeMaster
OfficeLoc1
OfficeLoc2
And so on?

 

[Cpinhey]

Hi,

Yes - the sub sheets should reflect the same data that is shown on the Master "All Projects" sheet. I confirm the sub sheets are the office locations ie BR,CO,DM.

Ideally, if I could click a button to run a macro to update all the sub sheets for each office with data from the master it would be perfect.

I've been playing with VBA Copy and paste range code, but, I'm new to VBA and just can't seem to get the syntax right and I'm not even sure thats the path I should be going down!

Any help will be greatfully received.

If it helps I can send you a screen print of the spreadsheet.

 

[Sthrncali]

Ok here you go:

You will need to set the myCol variable and change "Master Sheet" to the name of your actual master sheet.

Also this code will delete all data (rows 2 and beyond) and readd it from the master each time you run it. I was assuming you didnt have any extra worksheets other than the Master & Office Location sheets.

If you do have other sheets that need to be untouched, just let me know and I can modify it to meet your needs. Otherwise you should just be able to add this to a button and roll with it.

Sub MoveData()
Dim Destination As String
Dim myCol As Integer
Dim NextRow As Integer
myCol = 3  'This is the Col your Office Code reside in on your master sheet. A = 1, B = 2, and so on.
For Each ws In Worksheets
If ws.Name <> "Master Sheet" Then     'Change this to the name of your master sheet.
ws.Rows("2:65536").ClearContents
End If
Next
For r = 2 To ActiveSheet.Range("A65536").End(xlUp).Row
Destination = ActiveSheet.Cells(r, myCol).Value
NextRow = Worksheets(Destination).Range("A65536").End(xlUp).Offset(1, 0).Row
Worksheets(Destination).Cells(NextRow, 1).EntireRow.Value = ActiveSheet.Cells(r, 1).EntireRow.Value
Next
End Sub

 

[Cpinhey]

That is amazing - thank you so much.

I do have some other sheets in the spreadsheet that supply data to the master, so these really shouldn't be deleted. There are called:

"JUN BUSOBS", "JUL BUSOBS", AUG BUSOBS" and so on until "MAR BUSOBS".

Just to confirm - the data won't be deleted from the master will it?

 

[Sthrncali]

Ok, this modified code will only erase data on WS that appear in the Office Location Column of the Master Sheet.

Sub MoveData()
Dim Destination As String
Dim myCol As Integer
Dim NextRow As Integer
Dim ShName As String
Dim myRange As Range
myCol = 3  'This is the Col your Office Code reside in on your master sheet. A = 1, B = 2, and so on.
Set myRange = Worksheets("Master Sheet").Range("C") 'This should be the Col that contains Office Location Names. 
For Each ws In Worksheets
ShName = ws.Name
If Application.WorksheetFunction.CountIf(myRange, ShName) > 0 Then
ws.Rows("2:65536").ClearContents
End If
Next
For r = 2 To ActiveSheet.Range("A65536").End(xlUp).Row
Destination = ActiveSheet.Cells(r, myCol).Value
NextRow = Worksheets(Destination).Range("A65536").End(xlUp).Offset(1, 0).Row
Worksheets(Destination).Cells(NextRow, 1).EntireRow.Value = ActiveSheet.Cells(r, 1).EntireRow.Value
Next
End Sub

 

[Cpinhey]

That is great - thank you very much.

Any ideas why I'm getting a "Run Time Error 9 - Subscript out of range" error message?

It's probably me!

 

[Sthrncali]

Which line of code is highlighted?

 

[Cpinhey]

There is no line highlighted, just the message when I hit 'run'.

 

[Cpinhey]

Apologies - I've got to run (office is closing). Appreciate all your help - hopefully you'll have the final solution waiting for me.

Thanks again.