JimS
09-06-2009, 02:06 PM
Below is code that works fine but it is set up so that the worksheet must be active and selected.
When a particular Worksheet (Data worksheet) is "Deactivated" it calls for Macro1.
Macro1 runs Macro2 and then Macro3 & Macro4.
This whole process updates a chart automatically after the Data sheet is exited.
Can these Macros be modified so that worksheet "Project Timeline" does not have to be activated or selected?
Thanks for any help...
JimS
Sub Macro1()
Run ("Macro2")
Set i = Sheets("Project Data")
Set e = Sheets("Timeline Data")
Dim d
Dim j
d = 1
j = 2
Do Until IsEmpty(i.Range("D" & j))
If i.Range("D" & j) = "Active" Then
d = d + 1
e.Rows(d).Value = i.Rows(j).Value
End If
j = j + 1
Loop
Worksheets("Timeline Data").Activate
Run ("Macro3")
Run ("Macro4")
Sheets("Timeline Data").Visible = False
Sheets("Timeline").Select
End Sub
Sub Macro2()
Worksheets("Timeline Data").Activate
x = ActiveSheet.UsedRange.Rows.Count
Rows("2:" & x).Select
Selection.EntireRow.Delete
End Sub
Sub Macro3()
With ThisWorkbook.Sheets("Timeline Data")
On Error Resume Next
With Application.Intersect(.Range("J:J"), .Range("D:D").SpecialCells(xlCellTypeConstants).EntireRow)
.FormulaR1C1 = "=concatenate(RC2,"" / "", RC3,"" / "",RC1)"
End With
With Application.Intersect(.Range("J:J"), .Range("D:D").SpecialCells(xlCellTypeFormulas).EntireRow)
.FormulaR1C1 = "=concatenate(RC2,"" / "", RC3,"" / "",RC1)"
End With
On Error GoTo 0
End With
End Sub
Sub Macro4()
Cells.Select
Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1").Select
End Sub
When a particular Worksheet (Data worksheet) is "Deactivated" it calls for Macro1.
Macro1 runs Macro2 and then Macro3 & Macro4.
This whole process updates a chart automatically after the Data sheet is exited.
Can these Macros be modified so that worksheet "Project Timeline" does not have to be activated or selected?
Thanks for any help...
JimS
Sub Macro1()
Run ("Macro2")
Set i = Sheets("Project Data")
Set e = Sheets("Timeline Data")
Dim d
Dim j
d = 1
j = 2
Do Until IsEmpty(i.Range("D" & j))
If i.Range("D" & j) = "Active" Then
d = d + 1
e.Rows(d).Value = i.Rows(j).Value
End If
j = j + 1
Loop
Worksheets("Timeline Data").Activate
Run ("Macro3")
Run ("Macro4")
Sheets("Timeline Data").Visible = False
Sheets("Timeline").Select
End Sub
Sub Macro2()
Worksheets("Timeline Data").Activate
x = ActiveSheet.UsedRange.Rows.Count
Rows("2:" & x).Select
Selection.EntireRow.Delete
End Sub
Sub Macro3()
With ThisWorkbook.Sheets("Timeline Data")
On Error Resume Next
With Application.Intersect(.Range("J:J"), .Range("D:D").SpecialCells(xlCellTypeConstants).EntireRow)
.FormulaR1C1 = "=concatenate(RC2,"" / "", RC3,"" / "",RC1)"
End With
With Application.Intersect(.Range("J:J"), .Range("D:D").SpecialCells(xlCellTypeFormulas).EntireRow)
.FormulaR1C1 = "=concatenate(RC2,"" / "", RC3,"" / "",RC1)"
End With
On Error GoTo 0
End With
End Sub
Sub Macro4()
Cells.Select
Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1").Select
End Sub