I need to place a pause/delay in my code to allow my program to catch up

[fionajane]

Hi all, i have written some code, the idea is to format a spreadsheet that i receive every month so that it is in an acceptable format for me to copy and paste the info from this one excel file containing 7 sheets of information, into 7 seperate excel files to then be imported into access.

My code goes into error after i have formatted it, this happens when it gets to the part where i ask it to navigate to the seperate excel file and open the file.

I have checked the code by running through it with F8 and if i tap F8 all the way through my code it works sucesfully, its only when i leave it to its own devices that it cant seem to perform the action of opening my workbook (that i want to transfer the code into) quick enough before the next action (pasting) kicks in.

ITS ALL SOOOOOO CONFUSING!

any ideas please, or am i just being dumb?

[Phil_V]

any ideas please, or am i just being dumb?

Without seeing your code it's hard to tell
No, seriously though, unless you are doing something unusual the code will normally open the new workbook, and won't continue until that workbook is open. So in this instance it would be worth being able to see some of your code, (or a sample workbook), in order to help understand what is going on and what might be causing the error.

[fionajane]

Hi there,

sorry, i wasnt sure whether i needed to post the code. here it is: -

 ChDir _
        "C:\Documents and Settings\fiona.hanson\My Documents\xxxTHIS ONExxx\Expenses Monitoring\PreProject Cost Sheets\INDIVIDUAL"
    Workbooks.Open Filename:= _        "C:\Documents and Settings\fiona.hanson\My Documents\xxxTHIS ONExxx\Expenses Monitoring\PreProject Cost Sheets\INDIVIDUAL\1 Summary Table.xls"
    Range("A2").Select
    
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

The red bit of the code is where it starts to have a nervous breakdown! it does all the other actions i tell it too until there.

fiona x