I have a button that calls many separate macros. Everything works as expected, but with 1 exception.
I need to input a formula in row 2 of four separate columns and have the formulas drag down to the last populated row. In order to achieve this "easily" I've added a macro that will select from A1:AK700 (there should never be more than 500 rows so I went to 700 to be safe) and create a table. Then the formulas are input (I do the table so that the formulas automatically go down to the last populated row). I then have a macro to select the same range and convert it to range. Finally, I have a macro that runs after all this to copy and paste values and then take two of the tabs and create a new file with copies of those tabs.
Everything runs correctly, but at some point after converting to range (or around that part of the process) Excel slows down a lot, stops responding for a while and then finally starts again. Once everything as run, I go to both the original file and the newly created file and the table formatting is applied to the entire sheet (all the way down to cell ~1,048,000!). Clearly, this is why it's taking so long and ceases to respond, right?
How can I stop this?
I'm pasting the code in the order that it runs from the table creation point. I don't have very much experience writing macros (about a month) so any help would be appreciated!! And all of this does need to be done with a macro. They want it all to happen with just one button click.
Code:
Sub CreateTable()
Sheet1.ListObjects.Add(xlSrcRange, Range("A1:AK500"), , xlYes).Name = "Table1"
End Sub
Sub FormulaInput()
Sheets("Activity - Hires").Select
Range("AH2").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(NHO_Global!C[-21],MATCH('Activity - Hires'!RC[-24],NHO_Global!C[-33],0))"
End Sub
Sub FormulaInput2()
Sheets("Activity - Hires").Select
Range("AI2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(ISERROR(RC[-1]),RC[-15]=""Contingent Worker""),""Contingent Worker"",IF(AND(ISERROR(RC[-1]),ISNUMBER(SEARCH(""(Terminated)"",RC[-33]))),""Terminated"",IF(AND(ISERROR(RC[-1]),RC[-34]=TODAY()),""Hired Today"",IF(ISERROR(RC[-1]),""Under Investigation"",""""))))"
End Sub
Sub FormulaInput3()
Sheets("Activity - Hires").Select
Range("AJ2").Select
ActiveCell.FormulaR1C1 = "=IF(ISERROR(RC[-2]),"""",IF(RC[-2]<0,RC[-17],""""))"
End Sub
Sub FormulaInput4()
Sheets("Activity - Hires").Select
Range("AK2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(RC[-3]),"""",IF(RC[-3]<-10,""Obtain Manager Email"",""""))"
End Sub
Sub ConvertToNumber()
'This is so that the formulas work as the numbers in column J are used, but they're stored as text initially
Range("J:J").Select
With Selection
Selection.NumberFormat = "General"
.Value = .Value
End With
End Sub
Sub ConvertToRange()
Sheet1.ListObjects("Table1").Unlist
End Sub
Sub CopyPasteValuesActivity()
Worksheets("Activity - Hires").Select
Worksheets("Activity - Hires").Range("A1:AK700").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
Sub CopyPasteValuesNHO()
Worksheets("NHO_Global").Select
Worksheets("NHO_Global").Range("A1:Q700").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
Sub SaveSepFile()
Sheets(Array("Activity - Hires", "NHO_Global")).Select
Sheets("Activity - Hires").Activate
Sheets(Array("Activity - Hires", "NHO_Global")).Copy
End Sub
As indicated in the comments, I decided to remove the table creation and removal processes completely. I figured out how to have the formulas input and dragged down to the last populated row so I just did that. That code is below (for just one of the formulas):
Sub FormulaInput12()
Dim rng As Range
Dim sht As Worksheet
Dim LastRow As Long
Set sht = ThisWorkbook.Sheets("Activity - Hires")
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
Set rng = Range("AH2:AH" & LastRow)
rng.Formula = "=INDEX(NHO_Global!C[-21],MATCH('Activity - Hires'!RC[-24],NHO_Global!C[-33],0))"
End Sub
Doing this allows the file to process in just a few seconds and everything is correct.