Automate Excel tasks

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.
RishiSingh05Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RishiSingh05Author Commented:
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.
NorieAnalyst Assistant Commented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RishiSingh05Author Commented:
Yes, that would work.  Thanks.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

NorieAnalyst Assistant Commented:
Do you want the code to do the whole thing?

I was just finishing it off when you posted.
RishiSingh05Author Commented:
Sure ... please post the code.
NorieAnalyst Assistant Commented:
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

RishiSingh05Author Commented:
This works. Thanks.
RishiSingh05Author Commented:
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.
NorieAnalyst Assistant Commented:
Are you sure?

What were you thinking of instead of filtering?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.