0

I'm new to VBA and I'm trying to make a macro to copy data that meets specified conditions to other worksheets.

Here is an example closely related to my task. I have to select the main employee group and the person's name for each group, then move to the another sheet. I successfully selected each group name using the following code.

Sub Selecting the Staff Group Name
Dim MasterStaffGroup As String
Dim i As Long
Dim t As Long
Dim UsedR As Long

Sheets("Result").Activate
Sheets("Result").Cells.ClearContents
Cells(1, 1).Select

Sheets("Sheet1").Activate

UsedR = Worksheets("Sheet1").UsedRange.Rows.Count
MsgBox UsedR

Range("K1").Value = WorksheetFunction.CountIf (Sheets("Sheet1").Cells, "Staff Groups in this Master Staff Group: Staff Group Name")
t = Range("K1").Value
MsgBox t

Sheets("Sheet1").Activate
Cells(1, 1).Select

For i = 1 To UsedR
KeyW = Worksheets("Sheet1").Cells(i, 1).Value
Cells(i, 1).Activate
KeyW = Left(KeyW, 5)

If KeyW = "Staff" Then
ActiveCell.Offset(-1, 0).Select
MasterDoorGroup = ActiveCell.Text
ActiveCell.Copy
Sheets("Result").Activate
ActiveCell.PasteSpecial xlPasteValues
ActiveCell.Offset(0, 1).Select
Sheets("Sheet1").Activate
End If
Next i

End Sub

However, when I try to move people's names, I find it difficult. I found a template that fits my task, but I can't think of a way to get the RowCount. It would be ideal if I could count the row between "person" and "door". So I can sort the cells I need in Another Sheet.

Sub Template
For m = 1 To UsedR
If NameW = "Persons" Then
ActiveCell.Offset(1, 0).Select
ActiveCell.Resize(RowCount, 1).Select
Selection.Copy
Sheets("Result").Activate
ActiveSheet.Cell(m, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCpoyMode = False
ActiveCell.Offset(0, 1).Select
vSheets("Sheet1").Activate
End If
Next m
End Sub

What I'm missing is the row count as different groups will have different number of staff. I would like to ask about the code to get the row count. Also, if possible, I'd really like someone to give me some advice on whether this is the right way to select the staff names. Thank you so much.

Note: (1) The original data must remain on the original sheet. (2) Select, Activate, ActivateCell are more preferred as I am still learning to handle the complex code. However, it is always welcome to give an example that is without "select", "Activate", etc

WKTT
  • 1
  • 2
  • For your information, there are some supporting photos. [1] - [3] refer to the data set and [4] refer to the ideal view of the "Result" sheet [1]: https://i.stack.imgur.com/GuMFN.png [2]: https://i.stack.imgur.com/CjdIC.png [3]: https://i.stack.imgur.com/287bb.png [4]: https://i.stack.imgur.com/Y9MZS.png – WKTT Jul 26 '22 at 13:50
  • 1
    First and foremost, [avoid select and activate wherever possible](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Cyril Jul 26 '22 at 14:22

0 Answers0