Solved

Automate Excel tasks

Posted on 2012-03-25
9
433 Views
Last Modified: 2012-03-25
Part B:

This is a follow-on from Part A ref:

http://www.experts-exchange.com/Microsoft/Applications/Q_27647634.html#a37762682

A macro is needed which:

-       copies all the data from tab “Raw Data” to a new tab called “Raw Data – copy”

-       looks at Col D of “Raw Data – copy” and where it finds “Job Cost” it removes that row from “Raw Data – Copy” and copies it to a new tab called “Job Cost”.  So that when the macro is done executing, there will be no “Job Cost” rows in Tab “Raw Data – copy” as they would have all been moved to Tab “Job Cost”.

-      Insert totals in Tab “Job Cost” (as was done in Tab “Raw Data”)

Thanks.
0
Comment
Question by:RishiSingh05
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 

Author Comment

by:RishiSingh05
ID: 37762691
Note:  the totals in Tab "Raw Data - copy" will need to be adjusted accordingly since we are moving the "Job Cost" rows to a different tab.
0
 
LVL 34

Accepted Solution

by:
Norie earned 500 total points
ID: 37762700
Have you considered a slightly different approach?

You could filter all the JobCost rows to a worksheet and filter all the rows where JobCost isn't in column D to another worksheet?

Then you can use the code from the previous question to add totals to both of the new worksheets.
0
 

Author Comment

by:RishiSingh05
ID: 37762706
Yes, that would work.  Thanks.
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 34

Expert Comment

by:Norie
ID: 37762707
Do you want the code to do the whole thing?

I was just finishing it off when you posted.
0
 

Author Comment

by:RishiSingh05
ID: 37762726
Sure ... please post the code.
0
 
LVL 34

Expert Comment

by:Norie
ID: 37762727
Here you are.
Option Explicit

Sub FilterData()
Dim ws As Worksheet
Dim wsData As Worksheet
Dim wsJobCost As Worksheet
Dim wsOther As Worksheet
Dim rngData As Range
Dim rngCrit As Range
Dim LastRow As Long

    Set wsData = Worksheets("Raw Data")

    LastRow = wsData.Range("A" & Rows.Count).End(xlUp).Row

    Set rngData = wsData.Range("A1:H" & LastRow)

    Set rngCrit = wsData.Range("P1:P2")

    rngCrit.Cells(1) = wsData.Range("D1")

    rngCrit.Cells(2) = "JobCost"

    Set wsJobCost = Worksheets.Add

    wsJobCost.Name = "JobCost"

    rngData.AdvancedFilter xlFilterCopy, rngCrit, wsJobCost.Range("A1")

    Set wsOther = Worksheets.Add

    wsOther.Name = "Raw Data - Copy"

    rngCrit.Cells(2) = "=""<>"" & ""JobCost"""

    rngData.AdvancedFilter xlFilterCopy, rngCrit, wsOther.Range("A1")

    rngCrit.ClearContents

    ' add totals to worksheets
    For Each ws In Worksheets(Array("Raw Data", "JobCost", "Raw Data - Copy"))
        Call AddTotals(ws)
    Next ws

End Sub

Sub AddTotals(ws As Worksheet)
Dim rng As Range
Dim LastRow As Long



    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

    Set rng = ws.Range("E2:H" & LastRow)

    rng.NumberFormat = "#,000"

    Set rng = ws.Range("E" & LastRow + 1 & ":H" & LastRow + 1)

    rng.Formula = "=SUM(R2C:R[-1]C)"


    Set rng = ws.Range("I2:I" & LastRow)

    rng.Formula = "=SUM(E2:H2)"

End Sub

Open in new window

0
 

Author Comment

by:RishiSingh05
ID: 37762760
This works. Thanks.
0
 

Author Comment

by:RishiSingh05
ID: 37762768
Part C (final part) coming up.  Filtering can achieve the objective but I think the code will save me time as the actual data is fairly huge.
0
 
LVL 34

Expert Comment

by:Norie
ID: 37762877
Are you sure?

What were you thinking of instead of filtering?
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

734 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