Solved

Automate Excel tasks

Posted on 2012-03-25
9
427 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
  • 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 33

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 33

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 33

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 33

Expert Comment

by:Norie
ID: 37762877
Are you sure?

What were you thinking of instead of filtering?
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

This article will show you how to use shortcut menus in the Access run-time environment.
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

777 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