Automatically Update Data using VBA Code

Jacolene · May 25th 2016

Hi,

Please advise if it is possible that a VBA code automatically update the data if there is change made to the worksheets?

I am using the below code to add 38 sheets of data in the same workbook, to 1 sheet called "combined" (also in the same work book)

But if I make changes to data on any of the 38 sheets, the sheet "combined", does not update automatically.

So I need to know if there is a way that it will automatically update the "combined" sheet?

Code

Sub Jacolene()
Dim ws As Worksheet, i As Long, x As String
With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
End With
x = ""
Sheets.Add.Name = "Combined"
Sheets("Ward 1").Rows("1:3").Copy
Sheets("Combined").Range("A1").PasteSpecial xlPasteAll
Sheets("Combined").Tab.ColorIndex = 51
For Each ws In ActiveWorkbook.Worksheets
    Select Case ws.Name
        Case Is = "Control", "Summary", "Sizes - F", "Sizes - M", "Combined"
            GoTo zz
        Case Else
            ws.UsedRange.Offset(3).Copy
            Sheets("Combined").Range("B" & Range("C" & Rows.Count).End(3)(2).Row).PasteSpecial xlPasteValues
    End Select
zz:
Next ws
With Sheets("Control")
    For i = 5 To .Range("B" & Rows.Count).End(3).Row
        x = x & .Range("B" & i).Value & ", "
        x = x
    Next i
    x = .Range("E2") & " " & x
    x = .Range("D2") & " " & x
End With
Sheets("Ward 1").UsedRange.Copy
Sheets("Combined").Range("B1").PasteSpecial xlPasteFormats
Sheets("Combined").ListObjects.Add(xlSrcRange, Range("B3:Y2500"), , xlYes).Name = "StudentList3456789101112131415161718192021222324252627283031"
With Sheets("Combined")
    For i = .Range("C" & Rows.Count).End(3).Row To 4 Step -1
        Select Case .Cells(i, "C")
            Case Is = "", "Student Surname"
                .Rows(i).Delete
        End Select
    Next i
    .Columns.AutoFit
    .Columns("A").ColumnWidth = 2
    .Columns("B").ColumnWidth = 12.86
    .Columns("C").ColumnWidth = 30.86
    .Columns("D").ColumnWidth = 38.86
    .Columns("E").ColumnWidth = 8.71
    .Columns("F").ColumnWidth = 21.71
    .Columns("G").ColumnWidth = 22.14
    .Columns("H").ColumnWidth = 22.86
    .Columns("I").ColumnWidth = 26.43
    .Columns("J").ColumnWidth = 18.14
    .Columns("K").ColumnWidth = 13.14
    .Columns("L:N").ColumnWidth = 9.71
    .Columns("O").ColumnWidth = 64.71
    .Columns("P").ColumnWidth = 36.29
    .Columns("Q:S").ColumnWidth = 23.43
    .Columns("T").ColumnWidth = 36.29
    .Columns("U:W").ColumnWidth = 23.49
    .Columns("X").ColumnWidth = 99#
    .Columns("Y").ColumnWidth = 49.14
    .Rows(1).RowHeight = 42
    .Rows(2).RowHeight = 9.75
    .Rows(3).RowHeight = 36
    .Rows("4:5000").RowHeight = 19.5
    .Range("A4:AZ5000").Font.Name = "Century Gothic"
    .Range("A4:AZ5000").Font.Size = "11"
    .Range("A4:AZ5000").Font.Color = RGB(38, 38, 38)
    .Range("D1").Copy
    .Range("B1").PasteSpecial xlPasteFormats
    .Range("B1").HorizontalAlignment = xlLeft
    .Range("B2:B1500").HorizontalAlignment = xlCenter
    .Range("B1").VerticalAlignment = xlCenter
    .Range("D1").Value = Left(x, Len(x) - 2)
    .Range("D1").HorizontalAlignment = xlLeft
    .Columns("A:Z").NumberFormat = "0"
    .Columns("E:J").HorizontalAlignment = xlCenter
    .Columns("L:N").HorizontalAlignment = xlCenter
    .Columns("R:S").HorizontalAlignment = xlCenter
    .Columns("V:Y").HorizontalAlignment = xlCenter
    .Columns("C:D").HorizontalAlignment = xlLeft
    .Columns("K").HorizontalAlignment = xlLeft
    .Columns("O:Q").HorizontalAlignment = xlLeft
    .Columns("T:U").HorizontalAlignment = xlLeft
    .Columns("A:Z").VerticalAlignment = xlCenter
    .Activate
End With
ActiveWindow.DisplayGridlines = False
With Application
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
End With
End Sub

Display More

http://www.excelforum.com/show…68&highlight=#post4395568

cytop · May 25th 2016

Re: Automatically Update Data using VBA Code

There are 2 ways to do that:

1. Instead of pasting the values from each sheet to the Combined sheet, you could add a simple formula for each cell referring to the the source cell, including the worksheet reference. This will be slow to run, and with 38 sheets of 25 columns you'll end up with a lot formulas (Roughly 1000 times the number of total rows - so 1000 rows total mean nearly 1,000,000 individual formulas) that will affect the performance and size of the workbook. Also, it cannot take account of new additions.

2. You can have 1 event procedure which runs when a change is made anywhere in the workbook. However, it needs to be able to identify which item on the Combined sheet is to be updated. To do this, each item on the individual sheets must have some identifier. This identifier must be unique across all sheets (except the Combined sheet, of course).

Brief outline. There's not enough information in your post to give specifics.

Thank you for the link - your other thread is still to be updated.

Jacolene · May 25th 2016

Re: Automatically Update Data using VBA Code

Hi Cytop, I think I like your option 2 best, please advise what you mean by an identifier?

I have 38 sheets, exactly the same, but with different data (students information, like name, surname id number, gender etc.), so I used that code above, to combine all info from those 38 sheets to 1 worksheet, but, lets say I made a spelling mistake on a name from one of the 38 sheets, the code does not update the "combined" sheet automatically. I have to go and delete the tab, and run the code again

cytop · May 25th 2016

Re: Automatically Update Data using VBA Code

I would assume 'id number' is unique per student?

Post a workbook with some sample data of maybe 5 or so rows and 2 or 3 worksheets with anonymised data.

Jacolene · May 25th 2016

Re: Automatically Update Data using VBA Code

Hi,

I have attached the workbook

Each student will be given a student number, that can be used as a unique number, this is what I am trying to calculate in a different thread, that is where the PKW-APF1001 comes in

forum.ozgrid.com/index.php?attachment/69231/

cytop · May 25th 2016

Re: Automatically Update Data using VBA Code

And are the first characters of that ID, up to the Gender indicator, unique to each sheet?

If so, then the ID can be generated as soon as someone starts adding a new record, or at least after they have entered data into a cell on a new row. It would be a lot easier to generate a sequential number then.

Jacolene · May 25th 2016

Re: Automatically Update Data using VBA Code

The last 4 digits will be unique if it can be incremented, 1001,1002 for ward 1 and 2001,2002 for ward 2, and so on

How should I generate a sequential number?

cytop · May 25th 2016

Re: Automatically Update Data using VBA Code

I'll generate the number, or at least the code will. Time to cook dinner, I have all the information I need so I'll get back to this later.

cytop · May 25th 2016

Re: Automatically Update Data using VBA Code

Snag with your numbering scheme...

You said the number starts with 1 for Ward 1, 2 for Ward 2 etc... These are padded out to 4 digits for the sequential number part of the ID. No problem, except when it gets to Ward 10, that will presumably start '10', but when padded to 4 digits it will possibly duplicate Ward 1 numbers (...1001) if the gender is the same. Regardless, you are guaranteed duplicates with that scheme.

The workaround is that the ward number are padded to 2 digits, likewise the sequential numbers. Ward 1 numbers will start ...0101, ...0102 etc but Ward 10 numbers start ...1001, ...1002.

This is the only way to guarantee unique IDs.

cytop · May 26th 2016

Re: Automatically Update Data using VBA Code

Attached copy Updates the combined sheet when changes made on any of the Ward sheets. If you add a new item to the ward sheet, a sequential ID (numbered as above) will be generated as soon as the Gender is entered. THe Gender is part of the ID so cannot do that until then.

Once gender is entered and it has an ID, that row will be added to the Combined sheet.

This does not handle cases where a student is deleted. The combined table will have to be rebuild for that.

Jacolene · May 26th 2016

Re: Automatically Update Data using VBA Code

Thanks, but this is now getting to complicated for my brain, to even try and understand exactly what you did

cytop · May 26th 2016

Re: Automatically Update Data using VBA Code

Unavoidable, I'm afraid. That works as you wanted - It may be possible to write it a little more compactly or simply, but by the nature of things the analysis phase was very short so everything was written on the fly.

Regardless, even if someone spend some time analysing the logic and flow the end result would be very much the same, maybe slightly less code lines overall (invariably, more compact code is even more difficult to understand); but the logic would probably be identical.

Jacolene · May 30th 2016

Re: Automatically Update Data using VBA Code

Thanks for all your efforts, much appreciated!

Automatically Update Data using VBA Code

Participate now!

Share