Hi All,
I am working on two sheets in the same spreadsheet.
In Sheet 1, I want to only select rows that in the 3rd column have "Science and Engineering" written.
From those selected rows, I only want to copy cells in column 3, 6 and 5 (in this order) and copy them into Sheet 2 into columns 1-3 as Links, so that if any information in Sheet 1 gets updated, so will the respective info in Sheet 2.
After those were copied, I would like to sort the copied data in Sheet 2, in a descending order, based on values in Column 2 in Sheet 2.
So far I have code here that can select the rows that meet the criteria, and copy them all as links into Sheet 2 but I am struggling to copy only selected cells and add the sorting component to the code.
Private Sub CommandButton1_Click()
a = Worksheets("Sheet 1").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To a
If Worksheets("Sheet 1").Cells(i, 1).Value = "Science and Engineering" Then
Worksheets("Sheet 1").Range(Cells(i, 1), Cells(i, 3)).Copy
Worksheets("Sheet 2").Activate
b = Worksheets("Sheet 2").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Sheet 2").Cells(b + 1, 1).Select
ActiveSheet.Paste Link:=True
Worksheets("Sheet 1").Activate
End If
Next
Application.CutCopyMode = False
ThisWorkbook.Worksheets("Sheet 1").Cells(1, 1).Select
End Sub
Display More
I am a beginner with VBA and have managed to piece the above thanks to various YT tutorials but am struggling to find anything that would satisfy all this together - I am not sure whether this can be done to begin with?
I'd really appreciate any help, thanks in advance!