I am manipulating some data and to speed things up i decided to create a function to copy the relevant rows to another spreadsheet.
i was having trouble getting the function to work but could not find any reason for it. I changed it into a macro instead and it works fine when i run it manually from the drop down menu.. here is my code
Sub getRows()
Sheets("Dept. Data").Select
ActiveSheet.Cells.Select
Selection.ClearContents
Set origin = Sheets("NCMR Data")
Set Destination = Sheets("Dept. Data")
Dim Orow As Integer, Drow As Integer
Orow = 2
Drow = 2
Destination.Rows(1).Value = origin.Rows(1).Value
Do Until IsEmpty(origin.Range("B" & Orow))
If origin.Range("C" & Orow) = Range("'Departments'!D9") And origin.Range("T" & Orow) = Range("'Departments'!D10") And origin.Range("G" & Orow) = Range("'Breakdown'!L3") Then
Destination.Rows(Drow).Value = origin.Rows(Orow).Value
Drow = Drow + 1
End If
Orow = Orow + 1
Loop
End Sub
when i run the macro it is quick, and speeds up the calculations of my vba functions since it is only sorting through the relevant data. Problem is i want the macro to run automatically any time a user alters a cell because it may have to pull different data depending on what the user is asking for.
i figured i could do this by
Function getDeptData()
Application.Run "getRows()"
End Function
but when i do it this way everything is slow as snails.
not sure what the issue is with copying rows, but it seems strange that it would not work as a function, and then also does not work right when i call it as a macro through a function. but works fine when i manually run it as a macro. has anyone experienced this?
Bookmarks