Macro is So Close, but I Need Help

[samsilverman]

This is an Awesome Forum!!

Just recorded my first macro. It works pretty good, but I there are some errors in it. Here's what I want it to do.

1. Unprotect the "Areas" sheet so the macro can run.
2. Delete all info in given ranges in the "Invoices" sheet. (See macro below for ranges)
3. Filter the "Areas" sheet by the E Column; dates where cell is shaded green.
4. If no cell in E Column is shaded green, give some message and end macro.
5. If at least one cell in E Column is shaded green, copy up to 23 rows of info in columns B-H where the cell in Column E is green.
6. Paste (alt-H-V-V style) whatever is copied to cell B10 in "Invoices" sheet.
7. If there were only 1-23 rows to copy from "Areas" sheet, then unfilter "Areas" sheet, reprotect "Areas" sheet and then give some message and end macro at A1 of "Areas" sheet.
8. Else, macro should continue with instructions 5-6 above for any subsequent blocks of 23 rows each in the "Areas" sheet, pasting them to "Invoices" sheet cells, B50, B90 and B130.
9. After all copying and pasting is done, return to "Areas" sheet, unfilter, reprotect, give some message and then end Macro at A1 on "Areas" sheet.

I hope that is clear. Part of the problem, is that I don't know how to get the macro do the if/else thing. Two, I don't know how to enter the message coding. Three, I think when it filters my macro is only catching some of the rows because the filter hides some of them that aren't shaded green.

Thank You (x3) for any guidance!

ActiveSheet.Unprotect
Sheets("INVOICE").Select
Range("B10:H32").Select
Selection.ClearContents
Range("B50:H72").Select
Selection.ClearContents
Range("B90:H112").Select
Selection.ClearContents
Range("B130:H152").Select
Selection.ClearContents
Range("B170:H192").Select
Selection.ClearContents
Range("B210:H232").Select
Selection.ClearContents
Sheets("AREAS").Select
ActiveSheet.Range("$B$6:$H$80").AutoFilter Field:=4, Criteria1:=RGB(146, _
208, 80), Operator:=xlFilterCellColor
Range("B7:H38").Select
Selection.Copy
Sheets("INVOICE").Select
Range("B10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("AREAS").Select
Range("B41:H91").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("INVOICE").Select
Range("B50").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
Sheets("AREAS").Select
ActiveSheet.Range("$B$6:$H$80").AutoFilter Field:=4
Range("A1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("INVOICE").Select
End Sub

 

[BrianB]

Your code is unformatted an hard to read. It also needs comments to tell us what you expect it to do. Our time is limited here and it is difficult to understand other people's code. There are lots of different ways to do the same job.

In any case, you need to get into the habit of breaking down your project/code into different jobs or procedures and tackling them one at a time. In effect, a message should only include a question on one at a time too.

A good way of doing this is to put each operation in a separate sub routine and testing. It is easy to join them together with a top level sub() afterwards.

Hope this helps.