CODEWITHSUNDEEP
excelvbaspreadsheet
Jason
Jason

Reputation: 13

Excel working with 5 sheets and updating another sheet

I am creating a work sheet for my workers.. Each sheet is a name for that worker so like: James(sheet 1), Elver (Sheet 2), Chris (Sheet 3), John (Sheet 4) and then i have another sheet for another worker say (Mike) that makes things for these builders and are paid for it.

What i would like to do is in A1-A30 i enter something like (8x10) on James sheet (10x20) on Elver sheet etc... on Mikes sheet i want it to automatically go down the rows adding to Mikes sheet from A1 on James sheet or Elvers sheet every time i change a cell from A1-A30 on either of their sheets it automatically add's it to Mikes sheet

How can i do this i have been trying to figure it out for a week now..Macros? Linking? please help!

I have tried linking, equals etc.. but it's not what im looking for i just want to take any work sheets i make and anytime i enter data in a column it automatically loads it into a specific sheet going down that sheet

Upvotes: 1

Views: 70

Answers (2)

Chris Strickland
Chris Strickland

Reputation: 3490

I don't even think you need VBA as long as your version of Excel supports dynamic arrays. TEXTJOIN allows concatenating all of the texts in a range or set of ranges, and you can use that to generate an xml document and parse it into an array with FILTERXML. I have only used two sheets here (James and Elver), but you can add more at will. You would put this in Mike!A1:

=FILTERXML("<xml><x>" & TEXTJOIN("</x><x>", TRUE, James!$A$1:$A$30, Elver!$A$1:$A$30) & "</x></xml>", "//x")

Here it is formatted so that it is a little easier to see what's going on:

=FILTERXML(
  "<xml><x>" & 
  TEXTJOIN("</x><x>", TRUE, James!$A$1:$A$30, Elver!$A$1:$A$30) & 
  "</x></xml>", 
  "//x"
)

First, combine all of the texts using TEXTJOIN, using </x><x> as the separator. You're using this separator so that you can produce a valid xml string to use later.

TEXTJOIN("</x><x>", TRUE, James!$A$1:$A$30, Elver!$A$1:$A$30)

Wrap that with "<xml><x>" and "</x></xml>" to finalize the xml string.

="<xml><x>" & TEXTJOIN("</x><x>", TRUE, James!$A$1:$A$30, Elver!$A$1:$A$30) & "</x></xml>"

and pass the entire generated string to FILTERXML with the selector //x. This will return the text of all x nodes in your xml string as an array. x here was selected arbitrarily. You could use anything.

Here it is working on my machine. C1 just contains the text so you can see it:enter image description here

Let me know if that works for you.

Upvotes: 0

VBasic2008
VBasic2008

Reputation: 54777

A Workbook SheetChange

  • Select Developer > Visual Basic (F11). Copy the code to the ThisWorkbook module of the correct workbook. Exit the Visual Basic Editor.
  • When, in one of the worksheets from the array, a value in the range A1:A30 is manually changed, this (new) value(s) is written to the first available cell in column A of worksheet Mike.
Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    Dim Workers As Variant: Workers = Array("James", "Elver", "Chris", "John")
    Const WorkersAddress As String = "A1:A30"
    Const Chief As String = "Mike"
    Const ChiefColumn As String = "A"
    
    If IsError(Application.Match(Sh.Name, Workers, 0)) Then Exit Sub
    
    Dim wrg As Range: Set wrg = Intersect(Sh.Range(WorkersAddress), Target)
    If wrg Is Nothing Then Exit Sub

    Dim cws As Worksheet: Set cws = ThisWorkbook.Worksheets(Chief)
    Dim cCell As Range
    Set cCell = cws.Cells(cws.Rows.Count, ChiefColumn).End(xlUp).Offset(1)
    
    Application.EnableEvents = False
    
    Dim wCell As Range
    
    For Each wCell In wrg.Cells
        cCell.Value = wCell.Value
        Set cCell = cCell.Offset(1)
    Next wCell
    
    Application.EnableEvents = True
    
End Sub

Upvotes: 1

Related Questions