help to complex recorded macro of different formulas

RAM72 · Feb 21, 2016

Hi All

This is a recorded macro in which there is advanced filter copy cut and paste , together with array formula of index and match and sumproduct from 2 sheets namely workings and Summary report.

Require help to rewrite to a simpler elegant one.

Some adjustments need to be done with a cocktail of formula with advance filter , sumproduct ,index and match, rows still last row of data .

Can anyone help thanks

Code:

Sub uniquetest2()
'
' uniquetest2 Macro
'

'
Range("H1:J1").Select
Range(Selection, Selection.End(xlDown)).Select
Range("H1:J629").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"AE1:AG1"), Unique:=True ***(to adjust last data row)***

Columns("AE:AG").Select
Columns("AE:AG").EntireColumn.AutoFit
Range("AE1:AG1").Select ***(to adjust last data row)***
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Sheets("Summary Report ").Select
Range("A1").Select ***(to adjust last data row)***
ActiveSheet.Paste
Sheets("Workings").Select

Range("X1").Select ***(to adjust last data row)***
Selection.Copy
Range("P1:R1").Select
Application.CutCopyMode = False
Selection.Copy ***(to adjust last data row)***
Sheets("Summary Report ").Select
Range("D1").Select ***(to adjust last data row)***
ActiveSheet.Paste
Sheets("Workings").Select
Range("X1").Select ***(to adjust last data row)***
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary Report ").Select
Range("G1").Select
ActiveSheet.Paste
Range("H1").Select ***(to adjust last data row)***
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Sequence"
Range("H2").Select ***(to adjust last data row)***
Sheets("Workings").Select

Sheets("Summary Report ").Select ***(to adjust last data row)***
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT(Workings!R2C[12]:R629C[12],--(Workings!R2C8:R629C8='Summary Report '!RC1),--(Workings!R2C9:R629C9='Summary Report '!RC2),--(Workings!R2C10:R629C10='Summary Report '!RC3))" ***(to adjust last data row)***
Range("D2").Select ***(to adjust last data row)***
Selection.AutoFill Destination:=Range("D2:F2"), Type:=xlFillDefault
Range("D2:F2").Select ***(to adjust last data row)***
Selection.AutoFill Destination:=Range("D2:G2"), Type:=xlFillDefault
Range("D2:G2").Select ***(to adjust last data row)***
Range("G2").Select
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT(Workings!R2C[17]:R629C[17],--(Workings!R2C8:R629C8='Summary Report '!RC1),--(Workings!R2C9:R629C9='Summary Report '!RC2),--(Workings!R2C10:R629C10='Summary Report '!RC3))"
Range("D2:G2").Select ***(to adjust last data row)***
Selection.AutoFill Destination:=Range("D2:G124") ***(to adjust last data row)***
Range("D2:G124").Select

Range("H2").Select ***(to adjust last data row)***
ActiveCell.FormulaR1C1 = "=ROWS(R2C8:RC)"
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H124")
Range("H2:H124").Select


Sheets("Workings").Select

Range("Y2").Select
Selection.FormulaArray = _
"=INDEX('Summary Report '!R2C8:R124C8,MATCH(Workings!RC[-17]&Workings!RC[-16]&Workings!RC[-15],'Summary Report '!R2C1:R124C1&'Summary Report '!R2C2:R124C2&'Summary Report '!R2C3:R124C3,0))" ***(to adjust last data row)***

Selection.AutoFill Destination:=Range("Y2:Y629")
Range("Y2:Y629").Select ***(to adjust last data row)***

End Sub

YasserKhalil · Feb 21, 2016

Can you post a sample workbook to get what are you trying to achieve?

p45cal · Feb 21, 2016

What you're doing is built in to Excel. See attached. No macros in this because it's easy to do manually, although it can be automated also.
To get the details/origin of any sum you just need to double click that value (eg. double click cell AG15 which contains the value 687.48 and you'll see the rows it came from (you can safely delete the resulting new sheet)).

RAM72 · Feb 21, 2016

YasserKhalil said:
Can you post a sample workbook to get what are you trying to achieve?

See attached the columns are highlighted in yellow.

In summary advanced unique of columns Tariff Description Origin to report

Then summary of calculation of qty ,litres, amount of workings to summary report using sumproduct for qty 1 and amount.

Sequence using rows formula which need to match with their corresponding tariff ,description, origin in workings sheet itemised using array formula index and match .
It a bit complex as data goes to 1000 to 5000 rows which is not easy .

RAM72 · Feb 21, 2016

p45cal said:
What you're doing is built in to Excel. See attached. No macros in this because it's easy to do manually, although it can be automated also.
To get the details/origin of any sum you just need to double click that value (eg. double click cell AG15 which contains the value 687.48 and you'll see the rows it came from (you can safely delete the resulting new sheet)).

Your idea is good through pivot table but give a try and noticed that when duplicates tariff and description it remains blank and the origin appears .

I need to load these in an interface with their sequencing numbers which must tally with their workings .

Further this is a sample the real files are of 2000 rows to 7000 rows which makes me

and have a a least a dozen like these per day. It's a lot of pressure.
So if you have an idea how to fill the blank ,kindly advise . I have no time to copy and paste.

as this is dynamic

p45cal · Feb 21, 2016

RAM72 said:
Your idea is good through pivot table but give a try and noticed that when duplicates tariff and description it remains blank and the origin appears .

1. If you right-click on the Tariff header of the pivot table, choose Field settings…, click on the Layout & Print tab of the resulting dialogue box, then put a tick in the Repeat item labels tickbox, click OK, then do the same for Description, those blanks will be gone.
2. With your sample file source data, the pivot table reveals some descriptions which are essentially the same but are listed separately (with your original code too); some with a trailing space, some with 2 trailing spaces, some variants of spelling (eg. MOUTH WASH v. MOUTHWASH). Your data needs normalising. I have largely done this in the attached except for SHOWER GEL V. SHOWER GELS where I'm not sure whether they should be different or not.
Column X in the attached has no errors.

ps. If the final summary needs to be a plain table/range (ie. not a an updateable pivot table) that's easy to do.

When you record your steps in a macro when creating a pivot table the resultant code is very long and convoluted as well as being very specific. It can be considerably shortened and made more general.

RAM72 · Feb 21, 2016

Do agree there are trailing as its from another interface , there a lot to normalize as well as description.

However I adjust the sequence and works but when I change description needs to refresh manually.

Is there a way to make it dynamic we change data or add data or delete data
I not well versed with pivot table

p45cal · Feb 21, 2016

Right-click the pivot and click Refresh after adjusting the source data, although if you've increased the size of the source data you'll have to adjust its source data range.

In the attached, there's a button on the Workings sheet near cell Z1. Clicking it adds a new sheet with a pivot and updates column Y of the Workings sheet.
See also comments in the code to convert pivot to normal range.

It may be easier to click the button again after changing the source data!

RAM72 · Feb 22, 2016

p45cal said:
Right-click the pivot and click Refresh after adjusting the source data, although if you've increased the size of the source data you'll have to adjust its source data range.

In the attached, there's a button on the Workings sheet near cell Z1. Clicking it adds a new sheet with a pivot and updates column Y of the Workings sheet.
See also comments in the code to convert pivot to normal range.

It may be easier to click the button again after changing the source data!

I tested works as charm but as annexed I have 3 additional headers interface headers in sheet formats but same information needs to be extracted highlighted in yellow but columns numbers differs. Some has additional charges to be calculated that need to be summarised.

Is there a way to make the code modulable by headers
Same info needs to extracted
my daily tasks is not easy

p45cal · Feb 22, 2016

I'm not sure what you mean by this.
What are the additional charges you have to calculate and summarise?

RAM72 · Feb 23, 2016

p45cal said:
I'm not sure what you mean by this.
What are the additional charges you have to calculate and summarise?

The first model with macro is ok

I have simply the 2nd model today I worked .

Can you adjust the macro on this 2nd model please

I worked excel 2013 at home and excel 2007 at work.

Will it work on excel 2007 otherwise I will test it tommorrow and advise

p45cal · Feb 23, 2016

see attached

RAM72 · Feb 24, 2016

Thank you

However when I tested both pivot 2model and chan 3b of post 8 in excel 2007 I got a debug error for both

run time error 438 for both sample
Repeatall labels xl repeatlabels highlight in yellow.

Is it possible to adjust it for excel 2007 (at work),with excel 2013 at home no issue working smoothly

p45cal · Feb 24, 2016

It was introduced in Excel 2010

Code:

  '.RepeatAllLabels xlRepeatLabels 'remove and adjust the following lines:
  For Each pfn In rfs
    With .PivotFields(pfn)
      .Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
      .RepeatLabels = True
    End With
  Next pfn

RAM72 · Feb 25, 2016

Made adjustment

Compile error message

getting End with without with

Could you look in or I made a wrong adjustment in excel 2007

Code:

'.RepeatAllLabels xlRepeatLabels
  For Each pfn In rfs
    .PivotFields (pfn)
    .Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
    .RepeatLabels = True
    End With
  Next pfn

p45cal · Feb 25, 2016

You've missed out With (which isn't a surprise as that's what the error message is telling you!). Just compare what you have with the snippet I gave you!

RAM72 · Feb 25, 2016

I follow your instruction, started from scratch

Fet runtime error 438 object doesn't support this property or method
debug gives this line below in yellow

.RepeatLabels = True

I don' t know what wrong

p45cal · Feb 25, 2016

Aha.. I discover that that too is not in versions pre 2010!
Workarounds:
https://www.google.co.uk/search?q=....sO4bmUujSk6gH#q=.Repeat+Labels+excel+2007+vba
http://windowssecrets.com/forums/showthread.php/132760-Repeat-Item-Labels-in-Pivot-Tables-Excel-2007
http://www.contextures.com/xlDataEntry02.html#FillProg

help to complex recorded macro of different formulas

Member

Well-Known Member

Well-Known Member

Attachments

Member

Attachments

Member

Well-Known Member

Attachments

Member

Attachments

Well-Known Member

Attachments

Member

Attachments

Well-Known Member

Member

Attachments

Well-Known Member

Attachments

Member

Attachments

Well-Known Member

Member

Attachments

Well-Known Member

Member

Attachments

Well-Known Member