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
--~--~---------~--~----~------------~-------~--~----~
-------------------------------------------------------------------------------------
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
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
trialcopy.xls
Description: MS-Excel spreadsheet
