Macro that changes a particular column in pivot table.

[BSB]

Hi,

I'm executing Excel macros with VB as my front end
In my excel sheet...
i have pivot tables...
I need a macro that would change the particular column in the pivot table (say col K of the sheet ) . Change col K value from 1 to 0

I have 5 worksheets in one workbook.
On 3 worksheet i have to perform these operations....

can someone guide me

[kadghar]

Hi,

I'm executing Excel macros with VB as my front end
In my excel sheet...
i have pivot tables...
I need a macro that would change the particular column in the pivot table (say col K of the sheet ) . Change col K value from 1 to 0

I have 5 worksheets in one workbook.
On 3 worksheet i have to perform these operations....

can someone guide me

Use workbooks, worksheets, cells, range and value.
Lets say you have 3 books and you want to write "hello" in column K from the row 1 to 10 in the 1st, 3rd and 5th sheets of the first and second workbook.

sub writeHello()
dim i as integer
dim j as integer
 
for i = 1 to 2
     for j = 1 to 5 step 2
          for n = 1 to 10
               workbooks(i).worksheets(j).cells(n,11).value = "hello"
          next
     next
next
end sub

the range option is also useful, lets say you want to copy "A1:B5" in the first worksheet and paste it into a sheet called "PasteHere" in C5

Sub RangeTest()
with worksheets(1)
     range(.cells(1,1), .cells(5,2)).copy
end with
worksheets("PasteHere").cells(5,3).pastespecial -4163
'Note that -4163 is equivalent to the xlpastevalues (or something like that) since 
'its that constant's real value.
end sub

Well, hope that helps

[BSB]

thank u....it worked

Use workbooks, worksheets, cells, range and value.
Lets say you have 3 books and you want to write "hello" in column K from the row 1 to 10 in the 1st, 3rd and 5th sheets of the first and second workbook.

sub writeHello()
dim i as integer
dim j as integer
 
for i = 1 to 2
     for j = 1 to 5 step 2
          for n = 1 to 10
               workbooks(i).worksheets(j).cells(n,11).value = "hello"
          next
     next
next
end sub

the range option is also useful, lets say you want to copy "A1:B5" in the first worksheet and paste it into a sheet called "PasteHere" in C5

Sub RangeTest()
with worksheets(1)
     range(.cells(1,1), .cells(5,2)).copy
end with
worksheets("PasteHere").cells(5,3).pastespecial -4163
'Note that -4163 is equivalent to the xlpastevalues (or something like that) since 
'its that constant's real value.
end sub

Well, hope that helps