Loop for all worksheets

[ghan7650]

Hi,

Can someone please advise if the below code for vba is correct to loop through all worksheets?



Sub Master()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

Next ws

End Sub

[vandan_tanna]

Yes it is.

[ghan7650]

So, I am trying to loop through all worksheet in a workbook and copy the pivot tables that are on each worksheet. can someone please check to see if these codes are right?
I am getting a 'Method or Data Member member not found error'

Sub Master()

Dim ws As Worksheet
Dim SheetN As Long
Dim CellRef As Long

SheetN = 0
CellRef = 4

For Each ws In ThisWorkbook.Worksheets
SheetN = SheetN + 1
Worksheets.SheetSheetN.PivotTables("PivotTable1").TableRange2.Copy Destination:=Worksheets("Master").Range("CellRef")
CellRef = CellRef + 10
Next ws
End Sub

Ps. I have checked the code for copying the pivot tables and that works fine..

[vandan_tanna]

try changing

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Also, please use code tags around your code! read forum rules if you are not sure how to do this.

[ghan7650]

Please Login or Register  to view this content.

Hi Vandan,

The last part of the above formula where it says Range("CellRef"), I am trying to put the cell reference there, which is usually going to be Column A and some row number with 10 spaces in between.

The CellRef long has the value 4 initially but i want the lettter A in front of it to say .Range("A4") or .Range("A14) etc.

Any clue?

[vandan_tanna]

change

.range("Cellref")

to

.cells(cellref,1)


if this doesn't work, please upload your file so I can troubleshoot efficiently.

[ghan7650]

Hi Vandan,

That doesn't work either. Attached is the file.

There are more than 150 employees and so that many number of tabs in this worksheet but i have left only 3.

So in the code, i am trying to loop until all 150 tabs (employees) are captured and trying to copy the pivot table that you see on row 55 in the three employee tabs.

Thanks,

[vandan_tanna]

will this work:

Please Login or Register  to view this content.

[ghan7650]

i am getting an error saying that you can't move part of the pivottable or insert rows or columns in the pivottable.

What i am trying to do is capture all employees pivot tables in the master tab. but instead of doing it one by one, i want to try to loop it.

[ghan7650]

Any suggestions?

[vandan_tanna]

sorry about my half assed job ghan7650...I am trying to do this between meetings. Try this....

Also first clear the master sheet and then run the code. I get no errors when I run this.

Please Login or Register  to view this content.

[ghan7650]

Hi Vandan,

One quick question, I am trying to extract employee names from cell C3 of various tabs to the master tab and display them right on top of the pivot table on the master tab.

is this the right code?
(EmpName is defined as a String)

Please Login or Register  to view this content.

[vandan_tanna]

it isn't.

copy command copies content to clipboard and return true or false depending on success or failure
you can either

Please Login or Register  to view this content.

or do

Please Login or Register  to view this content.

[ghan7650]

I ran the second code that you have mentioned and it works like a charm.

Thanks for your help Vandan~!