MattyB1983
New Member
- Joined
- May 31, 2018
- Messages
- 5
Hi all.
I am little new to Excel coding and would really appreciate some help. I am writing an excel sheet for my students but getting stuck, essentially they will fill out a sheet scoring themselves from 1 to 5 in certain areas, Col A will have 50 skills, they will rate themselves 1 to 5 in each of these areas. If the Value is 1 (or 2 as well in this case but don't worry about that) in Col B, I want the macro to copy the skill set in Col A (same row) and then copy and paste it to another sheet for future self assessments. I have written the below that activates on a command button, however it is copy and pasting the entire row, I do not want this. I just want the content of Col A, to cut and paste into another Col A on a different sheet: as below. Thank you in advance to any experts out there as this is a bit beyond me.
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
a = Worksheets("GAPs").Cells(Rows.Count, 1).End(xlUp).Row
For i = 25 To a
If Worksheets("GAPs").Cells(i, 2).Value = "1" Then
Worksheets("GAPs").Rows(i).Copy
Worksheets("Post Gaps Sessions").Activate
b = Worksheets("Post Gaps Sessions").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Post Gaps Sessions").Cells(b + 1, 1).Select
ActiveSheet.Paste
Worksheets("GAPs").Activate
End If
If Worksheets("GAPs").Cells(i, 2).Value = "2" Then
Worksheets("GAPs").Rows(i).Copy
Worksheets("Post Gaps Sessions").Activate
b = Worksheets("Post Gaps Sessions").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Post Gaps Sessions").Cells(b + 1, 1).Select
ActiveSheet.Paste
Worksheets("GAPs").Activate
End If
Next
Application.CutCopyMode = False
ThisWorkbook.Worksheets("GAPs").Cells(1, 1).Select
Application.ScreenUpdating = True
End Sub
I am little new to Excel coding and would really appreciate some help. I am writing an excel sheet for my students but getting stuck, essentially they will fill out a sheet scoring themselves from 1 to 5 in certain areas, Col A will have 50 skills, they will rate themselves 1 to 5 in each of these areas. If the Value is 1 (or 2 as well in this case but don't worry about that) in Col B, I want the macro to copy the skill set in Col A (same row) and then copy and paste it to another sheet for future self assessments. I have written the below that activates on a command button, however it is copy and pasting the entire row, I do not want this. I just want the content of Col A, to cut and paste into another Col A on a different sheet: as below. Thank you in advance to any experts out there as this is a bit beyond me.
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
a = Worksheets("GAPs").Cells(Rows.Count, 1).End(xlUp).Row
For i = 25 To a
If Worksheets("GAPs").Cells(i, 2).Value = "1" Then
Worksheets("GAPs").Rows(i).Copy
Worksheets("Post Gaps Sessions").Activate
b = Worksheets("Post Gaps Sessions").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Post Gaps Sessions").Cells(b + 1, 1).Select
ActiveSheet.Paste
Worksheets("GAPs").Activate
End If
If Worksheets("GAPs").Cells(i, 2).Value = "2" Then
Worksheets("GAPs").Rows(i).Copy
Worksheets("Post Gaps Sessions").Activate
b = Worksheets("Post Gaps Sessions").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Post Gaps Sessions").Cells(b + 1, 1).Select
ActiveSheet.Paste
Worksheets("GAPs").Activate
End If
Next
Application.CutCopyMode = False
ThisWorkbook.Worksheets("GAPs").Cells(1, 1).Select
Application.ScreenUpdating = True
End Sub