Search for fontcolour, copy row to another existing workbook, to sheet with cellvalue-name

[Marloes]

Hello,

I'm kind of new to VBA and I have a question that I can't seem to google my way out of... (Excel 2007)

I have a kind of transporting material that I have to keep track of (exchanging them with either transporters or clients).
For this, I weekly manually check our orderdata against the freight documents, which specify if the material is exchanged or if someone has a debt to us.
At the moment I'm using a red font colour (-16776961) if material has not been exchanged and a green colour if it has.
See attached document.

Can someone (help me to) create a macro that:
- finds each row with the red font colour (-16776961) in a workbook named "exchanged directly"
- copies each red row to an existing other workbook named "administration", and places it on an existing sheet with sheetname = transporter-name (same name as the name in column G) in the first empty row (sheet has headers)
- changes the font colour of the pasted row to the Automatic black colour
- deletes the copied row from the "exchanged directly" workbook

The greatest difficulty fo me is the second part of the above question. I'm hoping someone can help me out with this.
Thanks!!

[rvasquez]

Hello there,

I believe the below code should work for you:
*Please note: You will need to change the text I have made red in the code below to the filepath for the Administration workbook on your computer. If you are unsure of the filepath you can always open the workbook and in a blank cell type = CELL("filename") and that should give you the location.

Please Login or Register  to view this content.

To use this code:

1. Close the Administration workbook if it is open
2. Open the Exchangeddirectly workbook
3. Press Alt+F8
4. Clear the macro name field
5. In the macro name field type the text AddToAdmin
6. Select the Create option
7. In between the Sub AddToAdmin() and End Sub copy and paste the above code
8. Anything that appears in green is a comment
9. Don't forget to change the filepath to you Administration workbook's filepath
10. Close out of Visual Basic
11. Press Alt+F8
12. Select the AddToAdmin macro
13. Select the Run option

Let me know if this works for you!

Thanks!

RVASQUEZ

[Marloes]

Hi RVASQUEZ,

Thank you so much for looking in to this!
I ran the macro, but stumbled on the following error:

Error 91: Object variable or With blockvariable not set, in line

Is it possible for you to alter it, so it will function as it's supposed to?
Kind regards,
Marloes

[rvasquez]

Hello there,

Try placing

Please Login or Register  to view this content.

above that line of code so that it looks like this

Please Login or Register  to view this content.

[Marloes]

Hello Rvasquez,

Sorry for the late reply. Unfortunately it still isn't working. I get the same error 91, on the same line as before.
The Administration file is opened by the macro, but nothing is copied to it or deleted from the Exchanged-directly file.

Here is the code at this time:

Please Login or Register  to view this content.

Thanks so much for your effort!
Marloes

[Marloes]

Hello,
I've been looking at the macro for a while now .
Could it be that variable c is not set?
After running the macro, the only thing that happens is the Administration file is being opened.
The active cells on both files (where the 'cursor' is) do not change.

Thanks,
Marloes

[rvasquez]

Hey there,

No c is declared as a range and is set to the current cell in the loop. Try just for the moment deleting the following lines and let me know what happens.

Please Login or Register  to view this content.

[Marloes]

Hello Rvasquez,

I changed the macro according to your post. Nothing more happened than before.
What did happen is that it ended up activating my Personal macro file (the macro was saved in that file, but i ran it from the Exchanged directly file).
It got me thinking, and I saved the macro in the Exchanged directly file and ran it directly from there. It worked like a charm :-)
Sorry for the trouble...!

I have one more question: if the Administration file doesn't contain a sheet with the correct transporter name, the row gets deleted from the Exchanged directly file but it doesn't get copied to the Administration file. Would it be possible to skip the row in the Exchanged directly file if the transporter name (x) does not exist?

I will go and try the macro on my original file as well.

Thanks so much!

[rvasquez]

Hello there,

Sorry about that I thought that was understood to place it in the exchangeddirectly worksheet by the line

2.Open the Exchangeddirectly workbook

Glad you could you get that sorted out!

As for this:

I have one more question: if the Administration file doesn't contain a sheet with the correct transporter name, the row gets deleted from the Exchanged directly file but it doesn't get copied to the Administration file. Would it be possible to skip the row in the Exchanged directly file if the transporter name (x) does not exist?

Try the below code, replacing the existing code between the Sub AddToAdmin and End Sub with the below code. What this does is if the worksheet name is not found then it highlights the cell's row black.
Let me know how it works!

Thanks!

Please Login or Register  to view this content.