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
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!