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.

[CODE=vb]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).wo rksheets(j).cel ls(n,11).value = "hello"
next
next
next
end sub[/CODE]

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

[CODE=vb]Sub RangeTest()
with worksheets(1)
range(.cells(1, 1), .cells(5,2)).co py
end with
worksheets("Pas teHere").cells( 5,3).pastespeci al -4163
'Note that -4163 is equivalent to the xlpastevalues (or something like that) since
'its that constant's real value.
end sub[/CODE]

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.

[CODE=vb]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).wo rksheets(j).cel ls(n,11).value = "hello"
next
next
next
end sub[/CODE]

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

[CODE=vb]Sub RangeTest()
with worksheets(1)
range(.cells(1, 1), .cells(5,2)).co py
end with
worksheets("Pas teHere").cells( 5,3).pastespeci al -4163
'Note that -4163 is equivalent to the xlpastevalues (or something like that) since
'its that constant's real value.
end sub[/CODE]

Well, hope that helps