Excel VBA Help needed.

[Mrsash]

Hello everyone.

I am working on some vba code I needed help with.
We access a passworded master.xlsx excel doc to pick a few row at a time. Most of the rows will have unallocated or a team members name in column A19 going down. Column B is crucial since it has various statuses, however important to us is "to be actioned".
Each of us use our own workbooks where we copy the rows we pick and change the unallocated to our name in the master and our personal workbook.

So when I run the macro from my workbook it needs to check the Master to show row count allocated to each team member with status "to be actioned". It also needs to show number of unallocated work with same status. We then enter the number we need in the inputbox and as long as the number is less than total number of unallocated to be actioned rows, copy it to our personal worksheets from A1:AI columns only changing them to our name in column A# accordinglyFuture

Can anyone help?

 

[Mrsash]

Ok I have got the basics working but need some help.

The filtered cells start from A19. The following code seems to change all rows in the visible range, but I need to only change number input with value mynum I entered earlier. I also need to copy only the rows changed to my workbook.worksheet. Hopes someone can help thanks

Source: Master.xlsx
Destination: Myworkbook.xlsx.

Dim rng As Range, cell As Range
    Set rng = ActiveSheet.AutoFilter.Range
    Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1)

    For Each cell In rng.Columns(1).Cells.SpecialCells(xlCellTypeVisible)
        cell.Value = "My Name"
    Next cell

 

[Mrsash]

I think I have solved the above issue with code chopping. What I have to update A19 down is as follows

Dim i As Integer
        Range("A18").Select
        For i = 1 To mynum                'mynum is the number of records to be taken up by one of us

            ActiveCell.Offset(1, 0).Select
                Do Until ActiveCell.EntireRow.Hidden = False
                ActiveCell.Offset(1, 0).Select
                Loop
            ActiveCell.Value = "My Name"
        Next

Like I said this works well. Next I need to copy only those rows from (A19) to (last row based on mynum,AI) range to my workbook
I was trying to get the cell address of the last cell updated to "My Name" by adding Crng = ActiveCell.Address() in the code, but this does not yield the correct address as per the watch window depending on where I place it.
Any help would be much appreciated.

thanks