Macro works fine on a button but hangs on Worksheet_Activate

[SBNUT]

I have created a macro that will copy data from one sheet and post to another sheet. Then sort the data, and create header rows and spaces. If I assign the macro to a button on the worksheet where I want the data, everything works fine. I would like this data to refresh everything I go into that sheet without having to hit a button. So I copied the code from the macro and pasted it into a module where I have a Worksheet_Activate that calls the code. The same code that works with the button is not working with the Worksheet_Activate. I have looked at the code and can not see the problem. I have attached the worksheet with both the button and the code to activate with the sheet is called. (The Worksheet_Activate is currently commented out so you can try the button to see what the data should look like) The code is in Sheet6 (Construction)

How the workbook works. The first sheet is the estimate sheet. We pick and choose items in this sheet that is needed for the job. Then we go into the "JobList" sheet. This only shows the values that were selected in the estimate tab. This does have a Worksheet_activate that produces this list and that is working correctly. The sheet that is not working correctly is the "Construction" tab. This should take the values in the "Job List" tab, copy it to the new sheet, sort it by cost type, and but some headers and blank rows in the sheet. This works correctly if you hit the button on the top right of the screen. However, if you go into the code and un-comment the Worksheet_Activate, the code does not work?

Private Sub Worksheet_Activate()
Call Construction1
End Sub

Sub Construction1()
'
'
Rows("8:685").Select
Selection.Delete Shift:=xlUp
Range("D14").Select
Sheets("Job List").Select
Sheets("Job List").Range("A8:J8").Select
Sheets("Job List").Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Construction").Select
Range("A8").Select
ActiveSheet.Paste
Range("A7:J7").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Construction").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Construction").Sort.SortFields.Add2 Key:=Range( _
"F8:F500"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Construction").Sort
.SetRange Range("A7:J500")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Dim i As Long

For i = Range("F" & Rows.Count).End(xlUp).Row To 9 Step -1
If Cells(i, 6) <> Cells(i - 1, 6) Then
Rows(i).Resize(3).Insert
Rows(7).Copy Rows(i + 2)
End If
Next i

End Sub

 

[SQUIDD]

What sheet is this code in.
not one of the sheets in the code itself ?

 

[SQUIDD]

My guess is your code is in one of the sheets you are calling in the macro. So every time your code selects the sheet, it calls the macro again in an infinite loop. One way around this is to re-write the code without sheet selection etc. Refer to the sheet but never select or activate it. There are some obvious parts of the code that could be deleted also, the whole code could be shortened.
But firstly if you could confirm if your code is in one of the sheets you are selecting in the code.
Dave.

 

[SBNUT]

Yes the code is in the sheet "Construction"

 

[JEC]

Which line is giving the error?

 

[SQUIDD]

Ok. So ever time you code selects sheet construction. It re-starts the macro.
I will look to re-weird it for you.

 

[SBNUT]

Thanks....

 

[JEC]

I think this will be enough behind your specific worksheet(probably your "Construction" sheet)

Private Sub Worksheet_Activate()
 With Sheets("Job List")
   Rows("8:685").EntireRow.Delete
  .Range("A8", .Cells(Rows.Count, "J").End(xlUp)).Copy Range("A8")
 End With

 Range("A7:J500").Sort Range("F8"), 1, , , , , , xlYes
End Sub

 

[SBNUT]

JEC,
Looks like you r code will delete the rows in worksheet "Job List" I need to delete the rows in worksheet "Construction" and copy the rows in "Job List". This code is getting called from "Construction" that is why you are probably confused on your code. This also a dynamic selection for the copy.

 

[JEC]

What does this. Hard to understand without desired outcome. This is the way but you need the right sheet reference in the code below

Private Sub Worksheet_Activate()
 With Sheets("Construction")
   .Rows("8:685").EntireRow.Delete
    Range("A8", .Cells(Rows.Count, "J").End(xlUp)).Copy .Range("A8")
   .Range("A7:J500").Sort .Range("F8"), 1, , , , , , xlYes
  End With
End Sub