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