Hi Ujjwal,
I think I have a solution for your problem.
But how can I attach files???
Regards,
Sergio
On 19 Apr., 11:38, Ujjwal Jha <jha.ujj...@gmail.com> wrote:
> Attached is the file on which macro was created and used.
>
>
>
>
>
> On Sun, Apr 19, 2009 at 2:52 PM, Ujjwal Jha <jha.ujj...@gmail.com> wrote:
> > Please find below the macro recorded:
>
> > Sub Trial()
> > '
> > ' Trial Macro
> > '
> > '
> > '
> > Range("A1:F16").Select
> > ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
> > "Sheet1!R1C1:R16C6").CreatePivotTable TableDestination:="",
> > TableName:= _
> > "PivotTable1", DefaultVersion:=xlPivotTableVersion10
> > ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
> > ActiveSheet.Cells(3, 1).Select
> > ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array( _
> > "Trading Relation", "CC", "GL", "TC"), ColumnFields:="Category"
>
> > ActiveSheet.PivotTables("PivotTable1").PivotFields("Amount").Orientation = _
> > xlDataField
> > ActiveWorkbook.ShowPivotTableFieldList = False
> > Application.CommandBars("PivotTable").Visible = False
> > Range("B4").Select
> > ActiveSheet.PivotTables("PivotTable1").PivotFields("CC").Subtotals =
> > Array( _
> > False, False, False, False, False, False, False, False, False,
> > False, False, False)
> > Cells.Select
> > Selection.Columns.AutoFit
> > Range("C4").Select
> > ActiveSheet.PivotTables("PivotTable1").PivotFields("GL").Subtotals =
> > Array( _
> > False, False, False, False, False, False, False, False, False,
> > False, False, False)
> > Range("I8").Select
> > Selection.End(xlToLeft).Select
> > Selection.End(xlToLeft).Select
> > Selection.End(xlToLeft).Select
> > Selection.End(xlToLeft).Select
> > Selection.End(xlToLeft).Select
> > Selection.End(xlToLeft).Select
> > Selection.End(xlToLeft).Select
> > Selection.End(xlToLeft).Select
> > Selection.End(xlToLeft).Select
> > Selection.End(xlToLeft).Select
> > Selection.End(xlToLeft).Select
> > Selection.End(xlToLeft).Select
> > Selection.End(xlToLeft).Select
> > Selection.End(xlToLeft).Select
> > Selection.End(xlUp).Select
> > Selection.End(xlUp).Select
> > Range(Selection, Selection.End(xlDown)).Select
> > Range(Selection, Selection.End(xlDown)).Select
> > Range(Selection, Selection.End(xlDown)).Select
> > Range(Selection, Selection.End(xlDown)).Select
> > Range(Selection, Selection.End(xlDown)).Select
> > Range(Selection, Selection.End(xlDown)).Select
> > Range(Selection, Selection.End(xlDown)).Select
> > Range(Selection, Selection.End(xlToRight)).Select
> > Range(Selection, Selection.End(xlToRight)).Select
> > Range(Selection, Selection.End(xlToLeft)).Select
> > Range("A3:H18").Select
> > Selection.Copy
> > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> > SkipBlanks _
> > :=False, Transpose:=False
> > Range("A4").Select
> > Application.CutCopyMode = False
> > Selection.AutoFilter
> > Selection.AutoFilter Field:=1, Criteria1:="="
> > Range("A6").Select
> > ActiveCell.FormulaR1C1 = "=+R[-1]C"
> > Range("A6").Select
> > Selection.Copy
> > Range("A7:A16").Select
> > Selection.SpecialCells(xlCellTypeVisible).Select
> > ActiveSheet.Paste
> > Application.CutCopyMode = False
> > ActiveSheet.ShowAllData
> > Range("A4").Select
> > Selection.AutoFilter Field:=1, Criteria1:="=*100-403*", Operator:=xlAnd
> > Range("A3:H8").Select
> > Selection.Copy
> > Workbooks.Add
> > ActiveSheet.Paste
> > Selection.Columns.AutoFit
> > Range("F4").Select
> > End Sub
>
> > On Sun, Apr 19, 2009 at 1:22 PM, Ashish Jain <26may.1...@gmail.com>wrote:
>
> >> Hi Ujjwal,
>
> >> Yes, you were able to make us understand. and the problem is Macro
> >> recorder is not enough intelligent to create loops for us.
> >> Now to better understand the problem, please post what you've recorded
> >> through macro.
>
> >> For more tips, tricks, downloads and problem solutions: visit -->
> >>http://www.excelitems.com
> >> ------------------------------------------------------------------------
> >> Regards
> >> *Ashish **Jain*
> >> Analyst, CSC
> >> Microsoft Certified Application (Excel) Specialist
> >> Excel and VBA Trainer
> >> Author ->www.excelitems.com
> >> +91-9999-40-48-43
>
> >> -------------------------------------------------------------------------------------
>
> >> -------------------------------------------------------------------------------------
>
> >> On Apr 19, 12:34 am, Ujjwal Jha <jha.ujj...@gmail.com> wrote:
> >> > Dear All,
>
> >> > I have a query. I am working on pivot table wherein the tabe has few
> >> values
> >> > in column A. Now if i record a macro where I select the first item after
> >> > giving it a filter. Copy the details of first item into a new excel
> >> sheet.
> >> > Then save the macro. Next time when i run the macro it will only take
> >> only
> >> > the first option.
>
> >> > Is there any way wherein when i run a macro it will open a new sheet for
> >> > every option in column A.
>
> >> > I hope i was able to make u understand.
>
> >> > --
>
> >> > Regards
> >> > Ujjwal Jha
>
> > --
>
> > Luv & Regards
> > Ujjwal Jha
>
> --
>
> Luv & Regards
> Ujjwal Jha
>
> trialcopy.xls
> 30KAnzeigenHerunterladen- Zitierten Text ausblenden -
>
> - Zitierten Text anzeigen -
--~--~---------~--~----~------------~-------~--~----~
-------------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
