Help with trying to copy rows into different tabs with conditions

[jrdunn78hawk]

Hi,

Apologies if this is in the wrong forum, I am hoping someone can help me with a a query.

I am trying to copy a row from one tab within an excel spreadsheet to another tab within the same spreadsheet with certain conditions.

I have attached the spreadsheet 'IM Project.xlsm' so you can understand what i'm trying to do and hopefully help me.

I am trying to copy any row in the 'risk register' and 'issue register' tabs that have a value of 'red' in the level column (F) into the 'Project Dashboard' tab. So far i have managed to change some code I got from a similar example to copy them over to the Project dashboard tab which I have also made run on startup. The code i am using is:

Sheet3 (Risk Register)
Sub Risks()
Dim LT As Long, j As Long
LT = Range("A" & Rows.Count).End(xlUp).Row

For j = 2 To LT
If Range("F" & j).Value = "Red" Then Rows(j).Copy Destination:=Sheets("Project Dashboard").Range("A" & Rows.Count).End(xlUp).Offset(1)
Next j
End Sub

Sheet4 (Issue Register)
Sub Issues()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
If Range("F" & i).Value = "Red" Then Rows(i).Copy Destination:=Sheets("Project Dashboard").Range("A" & Rows.Count).End(xlUp).Offset(1)
Next i
End Sub

ThisWorkbook
Sub WorkBook_Open()
Call Sheet3.Risks
Call Sheet4.Issues

End Sub

What I am finding is that every time the code is run it adds the risks & issues to the list and keeps adding whereas i want it to show them once (sorry if this is unclear, finding it hard to explain). Also I would like to split them up and be able to define where they start on the screen, i.e. I would like the risks to start in B3 and the issues to start in B20.

Any help would be appreciated, FYI this is the first time I have ever used coding like this so please bear with me.

Thanks

[rvasquez]

Hello there,

Attached is your original worksheet updated to hopefully accomplish what you were looking for. I have added comments that will appear in green to hepl you understand the code.

Let me know if this works for you!

Thanks!

RVASQUEZ

[jrdunn78hawk]

Hi rvasquez,

Thanks thats a massive leap towards where I want to be, I think I should have probably posted the attached spreadsheet titled 'IM Project Test' though as I want the red risks to go in the allocated 'major risk' section and the red issues in the 'major issues' section in the 'Dash Board' tab.

And I need all the other information to stay on the screen.

You would be a lifesaver if you could do that for me.

Thanks

[jrdunn78hawk]

Sorry, I actually just want the description cell and impact cell copied into the 'Dash Board' tab in the corresponding sections (major risks & major issues) and not the whole row.

Sorry to change the requirements there.

[rvasquez]

Hey there,
I think I got it , take a look and let me know how it works for you. There are some ways of shortening the code by using with sheet but for some reason I couldn't get my program to cooperate so I had to use the long way.

Thanks!

[jrdunn78hawk]

Hi there,

Thanks for your reply, it's nearly where I need it to be however, if I change one of the risks in the risk tab (i changed the top one) from red to green, save and then re-open it comes up with a debug message.

Also if possible I would like the 'major issues' section on the dashboard to be to the right of 'major risks', under 'tasks scheduled for the next period' rather than underneath the 'major risks'.

By the way I particularly like the way the boxes expand to fit in more risks, thats really good.

Thanks for your help on this its much appreciated, I had a look at the code and don't think there is anyway I could have done it on my own lol.

I have attached an updated version I'm working from(only updated formatting wise).

Thanks

[rvasquez]

Okay I think I got it for you.

I moved the on open event and the rest of the Sub procedures to Module1 so that they could be called from other worksheets.

So on the onopenevent it is now set to run the openmacro located in module1. Also if value is changed in D, E or F of the Risk Register or Issue Register worksheets then the openmacro runs as well. This way when you edit or update a record on these worksheets then the DashBoard will also automatically update. I wasn't able to recreate the error you go when closing and reopening the worksheet but test this one out and let me know if it works for you and if you still get that error.

Thanks!

[jrdunn78hawk]

One word mate.... Awesome!! lol

Only had a quick look but seems it doing exactly what I want it to do. Thanks very much for your help, will have a good look tomorrow but on face value it seems perfect.

Many many thanks

[rvasquez]

No problem! Don't forget to mark this thread solved and if I helped maybe give me a little start tap. Also, I will be unsubscribing from this thread so if an issue does arise please feel free to inbox me with a link back to it so I can help you.

Thanks!

[jrdunn78hawk]

Hi,

Have had a detailed look at this and its great. One little thing which I'm hoping you could change is - when i update the risks or issues it updates the sections on the dashboard tab which is perfect but it jumps to the dashboard tab after you update every individual risk or issue. If I wanted to for example update 10 issues on the issue tab it will jump to dashboard tab ten times and i would have to click back to the issue tab each time to update the next issue.

So - would it be possible for you to make it so that when you update or add an issue or risk on the tabs that it still updates the 'dashboard' tab but keeps the current tab in focus i.e. doesn't jump to the dashboard tab?

Have attached most recent version.

Many Thanks

[rvasquez]

Hello there,

To do this, merely press the F8 key on your keyboard while holding down the Alt Key to bring up the macro window. Select the "GetInfoIssue" macro and then select the "Step Into" option. Scroll all the way down to the second to last line and delete the following line of code.

Please Login or Register  to view this content.

The exit VBA. You may get a prompt that reads "This command will stop the debugger." Just select okay.

Let me know if this fixes your problem!

Thanks!

RVASQUEZ

[jrdunn78hawk]

Hi,

Just come back from holiday and picked up your response.

Many thanks I can confirm this did the trick.

Cheers