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