Macro is So Close, but I Need Help

samsilverman

Board Regular
Joined
Nov 9, 2009
Messages
166
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

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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.
 

Forum statistics

Threads
1,082,380
Messages
5,365,117
Members
400,824
Latest member
Themilkybarkid

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top