?
Solved

What does this excel macro do?

Posted on 2009-04-24
7
Medium Priority
?
339 Views
Last Modified: 2012-05-06
Hi,

What does this excel macro do?
Can anyone tell me what this exactly checks and gets...

REgards
Sharath

Sub Find_All_Users()
'Check for all CSC
'Local
Dim Cel As Range
 
thisWB = ActiveWorkbook.Name
Set row2 = ActiveSheet.Rows(2)
 
Workbooks.Add
newWB = ActiveWorkbook.Name
 
Workbooks(thisWB).Activate
row2.Copy
Workbooks(newWB).Activate
ActiveSheet.Paste
Workbooks(thisWB).Activate
 
ActiveSheet.Columns(5).Select
 
    For Each Cel In Selection
        If Cel.Interior.ColorIndex = 6 Then
            Cel.EntireRow.Copy
            Workbooks(newWB).Activate
            Range("A1").Select
            
                If ActiveCell.Offset(1, 0).Value = "" Then
                    ActiveCell.Offset(1, 0).EntireRow.Select
                    ActiveSheet.Paste
                Else
                    Selection.End(xlDown).Select
                    ActiveCell.Offset(1, 0).EntireRow.Select
                    ActiveSheet.Paste
                End If
            
            Workbooks(thisWB).Activate
        End If
    Next Cel
 
End Sub

Open in new window

0
Comment
Question by:bsharath
  • 3
  • 2
  • 2
7 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 24225976
It copies row 2 to a new workbook, then loops through each cell in column E and if the interior is yellow (colorindex 6), it copies that row to the new workbook as well, in the next available row.
Regards,
Rory
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24226022
Hello bsharath,

It copies row 2 of the activesheet in the containing workbook to a brand new wqorkbook.

It then continues to to copy every row from the original sheet where column F interior is colored an orangy yellow!

Regards,
Chris
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24226027
bsharath,

Sorry Rory i'm wrong ... and slow!

chris_bottomley
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24226059
Chris,
No worries - that describes most of my day today! :)
Rory
0
 
LVL 11

Author Comment

by:bsharath
ID: 24226065
Thank U...
And this code... I am a bit confused on these 2 ...
Sub Mark_All_Paid()
'Mark all CSC
'Local
Dim rng As Range, cell As Range
Set rng = Range("E1:E" & Cells(65536, "E").End(xlUp).Row)
rng.ClearComments
For Each cell In rng
If (cell.Interior.ColorIndex = 6 And cell.Value <> "") Then
cell.AddComment Text:="CSC User"
End If
Next cell
 
 
End Sub

Open in new window

0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24226122
It removes the existing comments and then loops through all the cells in E and adds a comment "CSC user" if the cell is yellow and not blank.
0
 
LVL 11

Author Comment

by:bsharath
ID: 24226212
Thank U
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question