Posts by Jacolene

Jacolene · May 30th 2016

Re: Automatically Update Data using VBA Code

Thanks for all your efforts, much appreciated!

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

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?

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/

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

Jacolene · May 25th 2016

Re: Adding Incrementing Numbers to a Formula

This one was solved already, how do I change it solved?

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

Jacolene · May 25th 2016

Greetings,

So I know the formula of taking the first letter of 2 words: =LEFT(J4,1)&E4 (this calculate the course they will be doing and their gender)

My complete formula so far is: =(Control!$G$2&"-"&Control!$H$2&LEFT(J4,1)&E4&RIGHT(Control!$B$6,1)), which looks like PKW-APF1

What I need now is to add incrementing number 001,002,003 etc. (without having to use a helper column)

In the end it should look like: PKW-APF1001, PKW-APF1002, PKW-APF1003 etc.

Jacolene · Apr 28th 2016

I want to change the maximum change under options > formulas to 0.001, but it keeps showing the error: your entry cannot be used. an integer or decimal may be required

Even my (point)/delete button on the keyboard will show a (point) in word when I use it, but in excel it shows a (comma)

I tried the below from an old thread from excel forum, but it didn't work

Open a new workbook
hit alt-f11 (to get to the vbe.
hit ctrl-r (to see the project explorer)
locate your workbook's project.

It should look like:
VBAProject(Book1)

Right click on that and select Insert|Module.
Paste this in:

Option Explicit
Sub testme()
With Application
.Calculation = xlManual
.Iteration = True
.MaxChange = 0.001
End With
End Sub

Put your cursor inside that macro (click on With).
Hit F5 (to run the code).

Then alt-f11 to get back to excel.
Can you get to tools|Options|calculation tab
(And change the calculation back to automatic, too.)