Dear Swapnil, I have tried it....it works... Thanks a lot!
Darwin 2011/10/26 Swapnil Palande <palande.swapni...@gmail.com> > Hi, > > 1. It is not necessary to define range, I have define it to avoide using > Sheets("2011-10-26").Range("A1") every time in the code. > After defining range, instead of Sheets("2011-10-26").Range("A1") i can > simply use rng object > > Try to give complete reference in the code > for ex: If you want to insert some value in range A1 of Sheet 1 then > Range("A1").value = "xyz" will only work if you you have focus in Sheet1 > > if you use Sheets("Sheet1").Range("A1").value = "xyz" will work > whether Sheet1 has focus or not > > 2. Do following code in changes in the code to create pivot table every day > If Sheet name format is fixed that is "yyyy-mm-dd" then make following > changes in the code (this is one time change, you do not have to do it every > day) > > Dim shtname as String > shtname = format(now(), "yyyy-mm-dd") > Set rng = Sheets(shtname).Range("A1") > > If Sheet name format is not fixed then use following code (this code you > have to change every day) > Dim shtname as String > shtname = "2011-10-26" > Set rng = Sheets(shtname).Range("A1") > > Regards, > > Swapnil. > > On Wed, Oct 26, 2011 at 12:01 PM, Darwin Chan <darwin.chankaw...@gmail.com > > wrote: > >> Swapnil, >> >> Thanks so much for your help. >> I could see you have added few codes.... >> >> ****** >> Set ptsheet = Worksheets.Add >> >> Set rng = Sheets("2011-10-26").Range("A1") >> >> ****** >> 1. Would like to ask....that means we have to define the range before >> creating pivot table? >> >> 2. What if i have to create this pivot table every day. What codes i wish >> to change Sheets("2011-10-26") as a variable? >> (I copy code from books and forum, I m just newbee) >> >> Thanks! >> >> Darwin >> >> 2011/10/26 Swapnil Palande <palande.swapni...@gmail.com> >> >>> Hi, >>> >>> Pls find attached excel. >>> >>> Regards, >>> >>> Swapnil. >>> >>> On Wed, Oct 26, 2011 at 11:23 AM, Darwin Chan < >>> darwin.chankaw...@gmail.com> wrote: >>> >>>> Dear all, >>>> >>>> Pls find the attached...and I couldn't find which button i could click >>>> for placing sample file. >>>> >>>> I can only place by replying the thread in gmail....did anyone know and >>>> thanks in advance!! >>>> >>>> Darwin >>>> >>>> >>>> 2011/10/26 Swapnil Palande <palande.swapni...@gmail.com> >>>> >>>>> Hi, >>>>> >>>>> It will be easy to solve issue if you provide sample data. >>>>> >>>>> In your code you have not defined DataField, without data field it will >>>>> show you blank pivot. >>>>> >>>>> Share sample data so that group can provide you correct code. >>>>> >>>>> Regards, >>>>> >>>>> Swapnil. >>>>> >>>>> >>>>> On Wed, Oct 26, 2011 at 10:07 AM, Chan Darwin < >>>>> darwin.chankaw...@gmail.com> wrote: >>>>> >>>>>> Dear all, >>>>>> >>>>>> I wrote the code in creating pivot table for my source of data. >>>>>> However, when run the code, it prompts with the message. >>>>>> >>>>>> "Run-time error '438': >>>>>> Object doesnt support this property or method" >>>>>> >>>>>> Below please find the code also. >>>>>> >>>>>> **************************** >>>>>> Sub CreatePivotTable() >>>>>> >>>>>> Dim PTCache As PivotCache >>>>>> Dim PT As PivotTable >>>>>> >>>>>> Application.ScreenUpdating = False >>>>>> >>>>>> 'Add a new sheet for the pivot table >>>>>> Worksheets.Add >>>>>> >>>>>> 'Create the cache >>>>>> Set PTCache = >>>>>> ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, >>>>>> SourceData:=Range("A1").CurrentRegion.Address) >>>>>> >>>>>> 'Create the pivot table >>>>>> Set PT = ActiveSheet.PivotTables.Add(PivotCache:=PTCache, >>>>>> TableDestination:=Range("A3")) >>>>>> >>>>>> 'Specify the fields >>>>>> With PT >>>>>> .PivotFields("street").Orientation = xlColumnField >>>>>> .PivotFields("condition_code").Orientation = xlColumnField >>>>>> .PivotFields("customer_code").Orientation = xlRowField >>>>>> .PivotFields("liner_code").Orientation = xlRowField >>>>>> .PivotFields("cont_type_code").Orientation = xlRowField >>>>>> .DisplayFieldCaptions = False >>>>>> End With >>>>>> >>>>>> Application.ScreenUpdating = True >>>>>> >>>>>> End Sub >>>>>> **************************** >>>>>> >>>>>> Thanks in advance!! >>>>>> >>>>>> Darwin Chan >>>>>> >>>>>> -- >>>>>> >>>>>> ---------------------------------------------------------------------------------- >>>>>> Some important links for excel users: >>>>>> 1. Follow us on TWITTER for tips tricks and links : >>>>>> http://twitter.com/exceldailytip >>>>>> 2. Join our LinkedIN group @ >>>>>> http://www.linkedin.com/groups?gid=1871310 >>>>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com >>>>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >>>>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >>>>>> >>>>>> To post to this group, send email to excel-macros@googlegroups.com >>>>>> >>>>>> <><><><><><><><><><><><><><><><><><><><><><> >>>>>> Like our page on facebook , Just follow below link >>>>>> http://www.facebook.com/discussexcel >>>>>> >>>>> >>>>> -- >>>>> >>>>> ---------------------------------------------------------------------------------- >>>>> Some important links for excel users: >>>>> 1. Follow us on TWITTER for tips tricks and links : >>>>> http://twitter.com/exceldailytip >>>>> 2. Join our LinkedIN group @ >>>>> http://www.linkedin.com/groups?gid=1871310 >>>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com >>>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >>>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >>>>> >>>>> To post to this group, send email to excel-macros@googlegroups.com >>>>> >>>>> <><><><><><><><><><><><><><><><><><><><><><> >>>>> Like our page on facebook , Just follow below link >>>>> http://www.facebook.com/discussexcel >>>>> >>>> >>>> -- >>>> >>>> ---------------------------------------------------------------------------------- >>>> Some important links for excel users: >>>> 1. Follow us on TWITTER for tips tricks and links : >>>> http://twitter.com/exceldailytip >>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com >>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >>>> >>>> To post to this group, send email to excel-macros@googlegroups.com >>>> >>>> <><><><><><><><><><><><><><><><><><><><><><> >>>> Like our page on facebook , Just follow below link >>>> http://www.facebook.com/discussexcel >>>> >>> >>> -- >>> >>> ---------------------------------------------------------------------------------- >>> Some important links for excel users: >>> 1. Follow us on TWITTER for tips tricks and links : >>> http://twitter.com/exceldailytip >>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >>> 3. Excel tutorials at http://www.excel-macros.blogspot.com >>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >>> >>> To post to this group, send email to excel-macros@googlegroups.com >>> >>> <><><><><><><><><><><><><><><><><><><><><><> >>> Like our page on facebook , Just follow below link >>> http://www.facebook.com/discussexcel >>> >> >> -- >> >> ---------------------------------------------------------------------------------- >> Some important links for excel users: >> 1. Follow us on TWITTER for tips tricks and links : >> http://twitter.com/exceldailytip >> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >> 3. Excel tutorials at http://www.excel-macros.blogspot.com >> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >> >> To post to this group, send email to excel-macros@googlegroups.com >> >> <><><><><><><><><><><><><><><><><><><><><><> >> Like our page on facebook , Just follow below link >> http://www.facebook.com/discussexcel >> > > -- > > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel > -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel