Automate Excel tasks
Part B:
This is a follow-on from Part A ref:
https://www.experts-exchange.com/questions/27647634/Automate-Excel-tasks.html?anchorAnswerId=37762682#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.
This is a follow-on from Part A ref:
https://www.experts-exchange.com/questions/27647634/Automate-Excel-tasks.html?anchorAnswerId=37762682#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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes, that would work. Thanks.
Do you want the code to do the whole thing?
I was just finishing it off when you posted.
I was just finishing it off when you posted.
ASKER
Sure ... please post the code.
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 SubASKER
This works. Thanks.
ASKER
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.
Are you sure?
What were you thinking of instead of filtering?
What were you thinking of instead of filtering?
ASKER