I am currently working with this code to automate some tasks for senior staff members that are not very adept in Excel. Wondering if VBA is simply not a very quick code or if my code is clunky and slow.
For clarity, I would think with how simple this code is it could run in under a second or two. Maybe this is overzealous?
Sub Paste() '---Paste Macro '---2016-05-23 Dim sht1 As Worksheet Dim sht2 As Worksheet Dim LastRow As Long Dim LastRow2 As Long Dim LastColumn As Long Dim StartCell1 As Range Dim StartCell2 As Range Dim rng1 As Range Dim rng2 As Range Set sht1 = GetWSFromCodeName("Sheet10") Debug.Print sht1.Name Set sht2 = GetWSFromCodeName("Sheet8") Debug.Print sht2.Name Set StartCell1 = Range("A2") Set StartCell2 = Range("B2") 'Find Last Row and Column LastRow = sht1.Cells(sht1.Rows.Count, StartCell1.Column).End(xlUp).Row LastColumn = sht1.Cells(StartCell1.Row, sht1.Columns.Count).End(xlToLeft).Column LastRow2 = sht2.Cells(sht2.Rows.Count, StartCell1.Column).End(xlUp).Row 'Select Range And Copy into Final Formula Sheet sht1.Range(StartCell1, sht1.Cells(LastRow, LastColumn)).Copy Destination:=sht2.Cells(LastRow2 + 1, 2) 'Convert Text in Column C of Final Formula Sheet to Numbers to Allow Advisor Code to Apply Set rng1 = Range(sht2.Cells(LastRow2, 3), sht2.Cells(LastRow2 + LastRow - 1, 3)) With rng1 .NumberFormat = "0" .Value = .Value End With 'Copy Advisor Function down to meet with new Pasted in Data With sht2 Set rng2 = .Cells(LastRow2, 1) End With With rng2 .Copy Destination:=Range(sht2.Cells(LastRow2, 1), sht2.Cells(LastRow2 + LastRow - 1, 1)) End With End Sub '---This Function allows the worksheet name to change in the workbook as it allows the 'user to set Worksheets to codename variables. By using this function the user can input a 'codename for a worksheet and the function will call the worksheet name of the corresponding 'codename, allowing the user to set worksheet variables to codenames without losing 'functionality usually associated with such variables. '---2016-05-23 Public Function GetWSFromCodeName(CodeName As String) As Worksheet Dim WS As Worksheet For Each WS In ThisWorkbook.Worksheets If StrComp(WS.CodeName, CodeName, vbTextCompare) = 0 Then Set GetWSFromCodeName = WS Exit Function End If Next WS End Function