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
