Solved: Run Macro against a non-active sheet

[JimS]

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


[vba]

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").SpecialCells(xlCellTypeConstants).EntireRow)
.FormulaR1C1 = "=concatenate(RC2,"" / "", RC3,"" / "",RC1)"
End With
With Application.Intersect(.Range("J:J"), .Range("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

[/vba]

[Bob Phillips]

[vba]

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

Run ("Macro3")
Run ("Macro4")

e.Visible = False
End Sub

Sub Macro2()
With ThisWorkbook.Worksheets("Timeline Data")

x = .UsedRange.Rows.Count
.Rows("2:" & x).Delete
End With
End Sub


Sub Macro3()
With ThisWorkbook.Sheets("Timeline Data")

On Error Resume Next
With Application.Intersect(.Range("J:J"), .Range("D").SpecialCells(xlCellTypeConstants).EntireRow)
.FormulaR1C1 = "=concatenate(RC2,"" / "", RC3,"" / "",RC1)"
End With
With Application.Intersect(.Range("J:J"), .Range("D").SpecialCells(xlCellTypeFormulas).EntireRow)
.FormulaR1C1 = "=concatenate(RC2,"" / "", RC3,"" / "",RC1)"
End With
On Error GoTo 0
End With
End Sub

Sub Macro4()
With ThisWorkbook.Worksheets("Timeline Data").Cells
.Sort Key1:=Range("E2"), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End Sub
[/vba]

[JimS]

xld,

Thanks, this is close - Macro4 fails with a "Method 'Range' of Object'_Global' failed".

I forgot to mention that the "Timeline Data" worksheet is always hidden.

Macro4 will run if I unhide and select the "Timeline Data" worksheet before running the Macro.

Any ideas?

Thanks again for your help...

JimS

[GTO]

Greetings Jim,

Just a missed keystroke as it needs a dot in front of .Range("E2").

Try this tiny adjustment:

[vba]
Sub Macro4()

With ThisWorkbook.Worksheets("Timeline Data")
.Cells.Sort Key1:=.Range("E2"), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End Sub
[/vba]

Hope that helps,

Mark

[JimS]

Nice find - works perfect.

Thanks to both of you...

JimS