Can't call several subs in a macro

[CesarC]

Hi! I am pretty new with macros and new in this forum,

I am trying to make several macros work in the same spreadsheet, but:

Call Worksheet_Change(ByVal Target As Excel.Range)

doesn't work.

Here is the whole code:

Sub InsertNewRowAndReorder()


If Not Target.Column = "7" Then Exit Sub
Application.EnableEvents = False



Call Worksheet_Change(ByVal Target As Excel.Range)
Call sbInsertingRows
Call Makro1


End Sub

Sub Worksheet_Change(ByVal Target As Excel.Range)


Me.UsedRange.Sort Key1:=Columns("D"), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom


Application.EnableEvents = True


Me.UsedRange.Sort Key1:=Columns("C"), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom


Application.EnableEvents = True
Me.UsedRange.Sort Key1:=Columns("A"), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom


Application.EnableEvents = True




End Sub

Sub sbInsertingRows()


Range("A2").EntireRow.Insert
End Sub

Sub Makro1()


Dim i As Integer


With ActiveSheet
totalRows = .Cells(.Rows.Count, "A").End(xlUp).Row


lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row


For i = 2 To totalRows
If .Cells(i, 100).Value > 0 Then
Number = .Cells(i, 19).Value
Do While Number > 0
lastRow = lasRow + 1
           
.Rows(i).Copy
.Rows(lastRow).PasteSpecial xlPasteValues
Number = Number - 1
Loop
End If
Next i
End With
End Sub

Any Ideas very or tips welcome, thanks!!

[Jan Karel Pieterse]

Worksheet_Change is an event macro and is not supposed to be called directly, but runs whenever a change is made to a cell on the worksheet the event belongs to.

[Paul_Hossler]

1. I added CODE tags for you around your macros - you can use the [#] icon to insert them next time and paste your macro between them

2. It's really not clear what you're trying to do. Can you describe it in words?

[SamT]

Some Basic VBA

One does not call a Change Event, One makes a change and that triggers the Event.
Some examples that will trigger a Worksheet Change Event:
Change the value of a cell
Insert a Row
Sort a Table

Setting Application.EnableEvents = True is only effective after setting it to False
Application.EnableEvents = False will prevent Changes from triggering a Change Event.
As written, your Sub Worksheet_Change(ByVal Target As Excel.Range) will trigger three Change Events, (Each Sort is a Change)

Sub Makro1 is very well written and should work. However, it will trigger many Change Events, equal in number to the Sum of all the values in column(19).

I hopt this helps you

[CesarC]

Thank you all, sorry, I am just starting with VBA. What I am doing is a database, you insert values in a row and when a value is inserted in column G, the whole row is arranged and a new empty row appears in row 2 (so you dont have to scroll all the way down to put new values)

I understood your comments and changed the code to:

Sub Worksheet_Change(ByVal Target As Excel.Range)

    If Not Target.Column = "7" Then Exit Sub
    Application.EnableEvents = False


      Me.UsedRange.Sort Key1:=Columns("D"), Order1:=xlAscending, _
      Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom


    Application.EnableEvents = True


      Me.UsedRange.Sort Key1:=Columns("C"), Order1:=xlAscending, _
      Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom


    Application.EnableEvents = True
      Me.UsedRange.Sort Key1:=Columns("A"), Order1:=xlAscending, _
      Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom


    Application.EnableEvents = True




    Range("A2").EntireRow.Insert


Dim i As Integer


With ActiveSheet
    totalRows = .Cells(.Rows.Count, "A").End(xlUp).Row


    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row


    For i = 2 To totalRows
        If .Cells(i, 100).Value > 0 Then
            Number = .Cells(i, 19).Value
            Do While Number > 0
                lastRow = lasRow + 1
           
                .Rows(i).Copy
                .Rows(lastRow).PasteSpecial xlPasteValues
                Number = Number - 1
            Loop
        End If
    Next i
End With
End Sub

It works better but doesnt copy a row in row 2 (has to be copied because the cell settings for the new row, including conditional formating)

Thanks!!!

[CesarC]

Oki, I was over complicating it. It works now, thanks!!

[mdmackillop]

If Not Target.Column = "7" Then Exit Sub

Target.Column will never be "7" . It may be 7, a numeric value.

lastRow = lasRow + 1

Typo; Use Option Explicit and declare all your variables.