Solved

Automate Excel tasks

Posted on 2012-03-25
9
429 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
My experience with Windows 10 over a one year period and suggestions for smooth operation
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

830 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