Good day all,

     I have been struggling to make a macro work latley and wanted to know if any of you could help...

The macro is named macro1, its short key is q....
At the end of the macro it goes to the next sheet (by sendkeys "^PGDN")
So, using sendkeys i have writen the following to have the macro run 10 times:

Application.SendKeys Keys:="^q", Wait:=True
Application.SendKeys Keys:="^q", Wait:=True
Application.SendKeys Keys:="^q", Wait:=True
Application.SendKeys Keys:="^q", Wait:=True
Application.SendKeys Keys:="^q", Wait:=True
Application.SendKeys Keys:="^q", Wait:=True
Application.SendKeys Keys:="^q", Wait:=True
Application.SendKeys Keys:="^q", Wait:=True
Application.SendKeys Keys:="^q", Wait:=True
Application.SendKeys Keys:="^q", Wait:=True

......but it runs fine the first time and then when it goes to the next sheet it just stops, no error msg or nothing. Just sits there like as if it did what i wanted it to.

Does anyone know of somekind of delay mechanism or method that can get this to work?

(Im running out of hair here pulling it out!!)

Are you trying to process something outside of Excel? Is the macro trying to rerun itself?
A macro won't rerun itself.

If you're calling a subroutine several times from within excel you should make it a separate sub routine:
-----------------------------------------------
Sub Macro1()

  Dim iCnt As Integer
  For iCnt = 1 to 10
    Call MyRoutine
  Next

End Sub

Private Sub MyRoutine()

  'Desired procedure to repeat here

End Sub
-----------------------------------------------


If you are just going through every sheet in the current workbook:
-----------------------------------------------
Sub Macro1()

  Dim oSheet As Worksheet
   For Each oSheet In ActiveWorkbook.Worksheets
     MsgBox oSheet.Name 'Displays name of each worksheet, your actual code goes here.
   Next

End Sub
-----------------------------------------------

Hope this helps

Thanks for the hand budy...

Yes I am processing something out of excel. I want to extract a ceartain set of files form this external program and insert that data in the worksheets 1-10 by copying the export to clipord and pasting into in the worksheet.

I tried your help and again the same result. It is as if it finishes after the fierst tab?!?!

Any more ideas?

Thanks again.

Hi there,
I have been looking through and thibnk you are missing little bit of code. From what I can see you are trying to loop through each worksheet then run your macro. The code above is correcty except it is missing the following

Sheets(oSheet.Name).Select

the code should look like

Sub Macro1()

  Dim oSheet As Worksheet
   For Each oSheet In ActiveWorkbook.Worksheets
    Sheets(oSheet.Name).Select

    'Your code goes here.

   Next

End Sub

'============

Has that helped?

Martin

Good day...

You guys rock!!!!

Worked just fine. Thanks for takeing the time to help me out, both of you!