Macro works fine on a button but hangs on Worksheet_Activate

SBNUT

New Member
Joined
Aug 25, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
1,936
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
What sheet is this code in.
not one of the sheets in the code itself ?
 

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
1,936
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

New Member
Joined
Aug 25, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows
Yes the code is in the sheet "Construction"
 

JEC

Well-known Member
Joined
Aug 21, 2021
Messages
566
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Which line is giving the error?
 

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
1,936
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Ok. So ever time you code selects sheet construction. It re-starts the macro.
I will look to re-weird it for you.
 

SBNUT

New Member
Joined
Aug 25, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thanks....
 

JEC

Well-known Member
Joined
Aug 21, 2021
Messages
566
Office Version
  1. 365
Platform
  1. Windows
I think this will be enough behind your specific worksheet(probably your "Construction" sheet)

VBA Code:
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

New Member
Joined
Aug 25, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Aug 21, 2021
Messages
566
Office Version
  1. 365
Platform
  1. Windows
What does this. Hard to understand without desired outcome. This is the way but you need the right sheet reference in the code below

VBA Code:
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
 

Forum statistics

Threads
1,148,177
Messages
5,745,194
Members
423,932
Latest member
pablo2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top