Excel VBA Help needed.

Mrsash

New Member
Joined
Feb 28, 2021
Messages
3
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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?
 

Attachments

  • Master.jpg
    Master.jpg
    73.3 KB · Views: 8
  • inputbox.jpg
    inputbox.jpg
    19 KB · Views: 8
  • Mybook.jpg
    Mybook.jpg
    49.4 KB · Views: 8

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Mrsash

New Member
Joined
Feb 28, 2021
Messages
3
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.


VBA Code:
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

New Member
Joined
Feb 28, 2021
Messages
3
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I think I have solved the above issue with code chopping. What I have to update A19 down is as follows

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,139
Messages
5,640,341
Members
417,140
Latest member
bdmprasenjit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top