Jun 05 2020 08:30 PM
Jun 06 2020 05:57 AM
@Suma_shankar Are you doing this in Excel or in Access?
If it's in Excel, you'll probably get better response if you post this in the appropriate Excel forum.
Jun 06 2020 11:16 AM
Instead of programmatically copying and pasting you can use VBA to write to a new cell directly. The following code eliminates the script from bouncing from sheet to sheet and reduces the number of steps. I'm sure others could write an even more simplified version but this should do the trick for you.
Sub oi()
Dim a, c As Long
Dim d, b As Range
With Worksheets("Sheet1").Range("B11:B96") 'Using a with block just helps to keep code cleaner when your using an object multiple times
c = Application.WorksheetFunction.Large(.Cells, 1) 'Your formula to find the largest value in the range (B11:B96)
Set b = .Find(c).Offset(0, 4).Cells
'Instead of copying the value we will assign b to be the cell that contains your reference using the highest value
'For the reference I used 4 (Column F) since I wasn't sure which column you were referring to
'Your reference will be -1 for column A, 0 for column B, 1 for C, 2=D, 3=E, 4=F, ...
End With
Set d = Application.Sheets("Sheet2").UsedRange 'This populates a range equal to all the cells in use on sheet2 (like Ctrl+A)
a = d.Cells.Rows.Count + d.Row 'This gives us the total number of rows plus the row the range starts from to give us the next empty row
Worksheets("Sheet2").Activate 'Moving on over to sheet2
Worksheets("Sheet2").Cells(a, 2).Select 'Locate the next cell in column 2 and select it
Selection.Value2 = b 'Make that cell equal to reference cell using the largest value you found
End Sub
Jun 06 2020 10:19 PM - edited Jun 07 2020 10:24 AM
SolutionJun 07 2020 12:58 AM
Jun 07 2020 01:20 AM
Jun 06 2020 10:19 PM - edited Jun 07 2020 10:24 AM
Solution