how to automatically update data in multiple sheets

**Eftychia** · 03-31-2014, 12:22 PM

Hi all,

If you see the attached file, you will see a workbook with 46 sheets. The first one the "master file" is the one to add data and those data I want to be distributed in the other 45 sheets based on the agent name I have in the column A (the 45 sheets are named by the agent name). All sheets are similar.
Every day I will add more data in master file and all the sheets should be automatically updated...

I cannot use vlookup since the common column is the agent name and I have many rows by the same name.
Is it possible?

I saved the attachment as xlsx because it was too big..

If anyone can help me I will be grateful...

**moinsk21** · 03-31-2014, 01:09 PM

Hi,

Please have a look to attached link it will be more helpful, as i question is similar.

http://www.excelforum.com/excel-programming-vba-macros/1000573-single-cell-multiple-worksheets.html#post3642274

Thanks!
Moinuddin Shaikh

If you like my Response:
1. Show appreciation to those who have helped you by clicking "* Add Reputation" below their posts.
2. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

Appreciate someone towards their career path is better than saying Thanks!
-Moin

**Eftychia** · 03-31-2014, 01:40 PM

Hi moinsk21,

Thanks for helping me.

The solution you provided in the link is "Select All Sheets and input the detail in one sheet, so it will update to all sheets". I think this doesn t work for me.

I need whenever I paste some raw data in "master file" sheet the rows where in column A is the "agent 1" to go to the sheet "agent1", the rows about agent2 to go to the sheet "agent2" etc.

**sktneer** · 03-31-2014, 01:43 PM

The following code will copy the data from the Master file to the corresponding sheet as soon as you start filling data in the Master file.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr As Long, lr1 As Long
Dim ws As Worksheet, ws1 As Worksheet
Set ws = ThisWorkbook.Sheets("Master file")
lr = ws.Cells(Rows.Count, 1).End(xlUp).Row
If Not Intersect(Target, Range("A" & lr & ":K" & lr)) Is Nothing Then
    For Each ws1 In Worksheets
        If ws.Range("A" & lr).Value = ws1.Name Then
            lr1 = ws1.Cells(Rows.Count, 1).End(xlUp).Row + 1
            ws.Range("A" & lr & ":K" & lr).Copy ws1.Range("A" & lr1)
        End If
    Next ws1
End If
End Sub

For detail, see the attached sheet. The sheet Master file is blank sheet. Just fill the data in it and see if the filled data is populated in the correct agent sheet.

**Eftychia** · 03-31-2014, 01:57 PM

hi sktneer,

Thank you for helping me!

I paste some rows with name agent1 & agent2 in the file you attached but nothing happened...(the rows didn t go to the agent1 & agent2 sheets)
I am not familiar with codes..Did I something wrong?

**sktneer** · 03-31-2014, 02:31 PM

Remember this code is for worksheet change event. So right click on the sheet Master file and select View Code and paste the code in the opened code window.

**sktneer** · 03-31-2014, 02:17 PM

The above code (Post#4) is helpful when you add a single row at a time in the master file. But if you are pasting multiple rows at once in the master file, try the code given below......

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr As Long, lr1 As Long
Dim ws As Worksheet, ws1 As Worksheet
Dim rng As Range, cell As Range
Set ws = ThisWorkbook.Sheets("Master file")
lr = ws.Cells(Rows.Count, 1).End(xlUp).Row
If Not Intersect(Target, Range("A:K")) Is Nothing Then
    Set rng = ws.Range("A2:A" & lr)
    For Each cell In rng
        cell.Select
        For Each ws1 In Worksheets
            If cell = ws1.Name Then
                lr1 = ws1.Cells(Rows.Count, 1).End(xlUp).Row + 1
                ws.Range(Cells(cell.Row, 1), Cells(cell.Row, 11)).Copy ws1.Range("A" & lr1)
                Exit For
            End If
        Next ws1
    Next cell
End If
End Sub

**Eftychia** · 03-31-2014, 02:40 PM

still it doesnt work... Did it work in your workbook?

**sktneer** · 03-31-2014, 11:47 PM

Modified the code. Please find the attached sheet to see if this works as per your requirement. To view the code on the sheet, right lick on the sheet "Master file" and select View Code.

**Chalkie** · 04-01-2014, 11:07 AM

I have something similar I am struggling with (below novice).

I have a master sheet and 102 'advisor named sheets'. The master sheet is updated daily with information (number and text) along 1 row in differnt columns. I would like when the advisors name is typed =joebloggs! for this whole row to be auto input on joebloggs sheet. There can be multiple of these entered daily for same person, so the information would need to populate on the next row so not to type over the previous entry.

I can't use macros or anything like that so I need very basic explainations.

**sktneer** · 04-01-2014, 11:37 AM

Please open a new thread and also upload a sample workbook with sample data in it.

**Eftychia** · 04-01-2014, 02:13 PM

Oh my God!! It works!!!
Thank you sktneer! So happy

I decided! I want to learn macros!

**sktneer** · 04-01-2014, 08:43 PM

Glad to help you. Thanks for the feedback.

how to automatically update data in multiple sheets

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

how to automatically update data in multiple sheets

Re: how to automatically update data in multiple sheets

Re: how to automatically update data in multiple sheets

Re: how to automatically update data in multiple sheets

Re: how to automatically update data in multiple sheets

Re: how to automatically update data in multiple sheets

Re: how to automatically update data in multiple sheets

Re: how to automatically update data in multiple sheets

Re: how to automatically update data in multiple sheets

Re: how to automatically update data in multiple sheets

Re: how to automatically update data in multiple sheets

Re: how to automatically update data in multiple sheets

Re: how to automatically update data in multiple sheets

Thread Information

Users Browsing this Thread

Similar Threads

multiple data validation list want to update automatically excel 2010

[SOLVED] How to Automatically update data in other sheets in a workbook ?

[SOLVED] Automatically populate data to multiple sheets

Update Data in Multiple Sheets From 1 Source Sheet

Formulas to automatically update on multiple sheets

Bookmarks

Bookmarks

Posting Permissions