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?

Improve company productivity with a Business Account.Sign Up

x
 
NorieConnect With a Mentor VBA ExpertCommented:
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
 
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.
0
 
RishiSingh05Author Commented:
Yes, that would work.  Thanks.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
NorieVBA ExpertCommented:
Do you want the code to do the whole thing?

I was just finishing it off when you posted.
0
 
RishiSingh05Author Commented:
Sure ... please post the code.
0
 
NorieVBA ExpertCommented:
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
 
RishiSingh05Author Commented:
This works. Thanks.
0
 
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.
0
 
NorieVBA ExpertCommented:
Are you sure?

What were you thinking of instead of filtering?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.