How to code right mouse click events into a macro

Posted by Will O'Donovan on April 04, 2001 1:02 AM

I Have created a macro to open up delimited text files ito excel, format them and continue until there are no more text files. Unfortuneatly each file is opened into a new workbook and I need them as multiple sheets of the first workbook.
If i can generate another step or seperate macro to move the worksheets into the the first book that would be great but i can't seem to emulate the right mouse click events that causes the moving of sheets between books manually.
Can anyone help. Many thanks in advance.

Posted by Dave Hawley on April 04, 2001 4:13 AM

Hi Will

Is this what you mean ?

Sub Copy1()
Dim ThisBook As String
ThisBook = ThisWorkbook.Name & ".xls"

Sheets("Sheet2").Copy Before:=Workbooks("MyBook.xls").Sheets(1)

Windows(ThisBook).Activate

End Sub

Sub CopyAll()
Dim Sht As Worksheet

ThisBook = ThisWorkbook.Name & ".xls"

Application.ScreenUpdating = False

For Each Sht In ThisWorkbook.Worksheets
Sht.Copy Before:=Workbooks("MyBook.xls").Sheets(1)
Next

Application.ScreenUpdating = True

Windows(ThisBook).Activate

End Sub

DaveOzGrid Business Applications

Posted by Dave Hawley on April 04, 2001 4:15 AM

Oops , That's a Mess! Use This

Sub Copy1()
Dim ThisBook As String

ThisBook = ThisWorkbook.Name & ".xls"

Sheets("Sheet2").Copy Before:=Workbooks("MyBook.xls").Sheets(1)

Windows(ThisBook).Activate

End Sub