Macro works in stepping through, but not when I run it - Excel 2003

[NVB1]

My macro runs fine if I use the F8 key and just step through all of it. When I run the macro I run into two problems:
1. The first problem occurs when I create a pivot table. When the code gets to the Set PT line below, I get an error that starts with "The PivotTable field name is not valid." I have changed all the field names to be letters only. All of the columns have label, the code is selecting the correct range, the first column does not have any blanks, I have tried using different ways of referencing the destination location and different ways of referencing the Table Name (including "PivotTable1") and regardless of what I do, I get the error. However, if I just use the F8 key to step through the lines below, the pivot table is created exactly where and how I want it to be.

Set PRangeOBC = WSD.Cells(1, 1).Resize(FinalRow, 8)
Set PTCache = ActiveWorkbook.PivotCaches.Add (SourceType:=xlDatabase, SourceData:=PRangeOBC.Address)
Set PT = PTCache.CreatePivotTable(TableDestination:="'[Reports Macro Master.xls]RepWeek'!R3C1", TableName _
:="WeeklyPT", DefaultVersion:=xlPivotTableVersion10)

2. Once the table is created I loop through a number of page fields, using filters to get data and then paste it into another workbook. Again, if I just keep hitting the F8 through my loop, everything works and the correct data is copied and pasted where I want it to be. However, if I just press F5 to continue through the loop to create all the reports, I end up with data in the wrong places, or data that isn't copied over at all, but no errors are reported.

Does anyone have any suggestions of what to try to correct these two problems.

Thank you for your help!

 

[mooseman]

Only that it is possible that your macro is going faster than Excel can create things.
when you F8, you wait for the line of code to execute, then F8 the next line.
When you run the code, the macro doesn't wait for each line to execute fully before continuing
Try putting some pauses in the code

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) is needed to preform the pause

just add this line whereever you need to pause (the number is in milliseconds)
Sleep 1000

 

[NVB1]

Thank you for the suggestion. I have now had a chance to try adding the Sleep 1000 code but it doesn't seem to solve the problem. I have tried up to sleep 3000, but it doesn't seem to help. I can still step through the code manually and it works just fine.
Any other thoughts?
Thanks!

 

[mooseman]

When you step through the code, does any of the steps take a long time to run?
Try putting a break point after the lines that give you errors when you run it, then F8 past that point to see if it works.....
I have a macro that vcreates a pivot table from an SQL cube and it takes about8 or 9 seconds to run, so you really have to make sure that your giving the macro time to do the work before continuing on to the next command or it may error out since the object you are creating isn't there to be referenced.

 

[NVB1]

Thank you so much for your help!!! The Sleep helped with some of my issues and then I also figured out that I needed to set ManualUpdate back to false. The one big problem is that I still can't create the pivot table when I just run the code, but it works fine when I step through in debug mode. Here is the section of code that seems to be the problem. The underlined portion is the line of code where the macro stops:

FinalRow = WSD.Cells(65536, 1).End(xlUp).Row
Set PRangeOBC = WSD.Cells(1, 1).Resize(FinalRow, 8)
Sleep 3000
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRangeOBC.Address)
Sleep 3000
Set PT = PTCache.CreatePivotTable(TableDestination:=PTWeek.Range("A3"), TableName:="PivotTable1")
Sleep 3000

This is early on in the program and I haven't even added the fields to the table at this point. It doesn't seem to matter what number I use in the Sleep code. Any other thoughts?

Again, thank you so much for your help!! It is really appreciated.