Filling in multiple sheets in another workbook while filling in a sheet in a master workbook

[Nurzhan]

Hallo,
What can you suggest for the following?

Description: There are 2 workbooks, workbook A (wbA) and Workbook B(wbB).
wbA - is a summary file, which has several columns filled in on daily basis. Each row in column A contains sheet names of wbB.

https://ibb.co/kBcghU

wbB - is a workbook with multiple sheets with names same as cell contents in column A in wbA.

https://ibb.co/mzJRGp

So the task is to copy contents of rows in wbA (columns B to S) to corresponding sheets in wbB( should be pasted in columns A to R). The contents of wbA are updated every day and it will be collected downwards and the number of rows added will not be constant. Whalt could be the best and simple way of accomplishing it? to user form? First fill in a user form, which will distribute the date to corresponding workbooks?

 

[mumps]

How is the data updated every day? Is the data entered manually row by row? If so, will column S always have a value entered?

 

[Nurzhan]

It's entered manually. One day maybe only 5 rows are filled and the other day 10 rows may be filled. Let's assume that column S will have a value always.

 

[mumps]

Make sure that both workbooks are open. Copy and paste this macro into the worksheet code module. Do the following: right click the tab for the sheet containing the data to be copied in wbA and click 'View Code'. Paste the macro into the empty code window that opens up. If necessary, change the workbook name highlighted in red in the code to suit your needs. Close the code window to return to your sheet. The macro is triggered automatically when you enter a value in column S and exit the cell. So it is important that columns A to R be filled in first and column S is filled in last.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("S:S")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim desWS As Worksheet
    Set desWS = Workbooks("[COLOR="#FF0000"]wbB.xlsx[/COLOR]").Sheets(CStr(Range("A" & Target.Row).Value))
    Range("B" & Target.Row & ":S" & Target.Row).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0)
    Application.ScreenUpdating = True
End Sub

 

[Nurzhan]

Wow! Awesome! I expected some kind of long procedure! Thanks so much!

 

[Nurzhan]

when a row deleted an error appears. Can I use "On Error GoTo Next and GoTo 0" as a solution?

 

[mumps]

I tried the macro on two dummy workbooks, deleting a row in wbA and I didn't get an error. How you are deleting the rows? What is the error message and which line of code is highlighted when you click "Debug"? I think that it would be easier to help and test possible solutions if I could work with your actual files which include any macros you are currently using. Perhaps you could upload a copy of your 2 files to a free site such as www.box.com or www.dropbox.com. Once you do that, mark each file for 'Sharing' and you will be given a link to each file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[Nurzhan]

Now I figured out what the real problem is. Actually an error occurs when column S is changed while column A is still empty. Because VBA cannot find the worksheet with a blank name)).

 

[mumps]

That would certainly cause an error. As I mentioned in Post #4 :

So it is important that columns A to R be filled in first and column S is filled in last.