VBA Open, copy, paste, close

[JazzmanWA]

Hi All,

I've been getting great tips on this board for several months. This time I can't find what I need.

Here is what I want to do...

I am opening a new workbook and pasting information into that workbook so, it may be called "book1" or "book12" depending on how many workbooks I create that day. I need to run a macro that opens an existing workbook, copies a worksheet to this new workbook I have already open and then closes the workbook I got the info from.

Here is what I have that works so far but, only if my workbook is "book1". How do I get this to copy that info to whatever my current workbook is called?


' Get_mail Macro

Workbooks.Open Filename:= _
"C:\Documents and Settings\XXXXXX\Desktop\Vendor email list.xlsx"
Sheets("Mailinfo").Select
Sheets("Mailinfo").Copy Before:=Workbooks("Book1").Sheets(2)
Windows("Vendor email list.xlsx").Activate
ActiveWindow.Close
Sheets("Sheet1").Select

Any help would be much appreciated. This is the "last piece of the puzzle" to get my macro to do everything I want it to do.

Thanks!

 

[JazzmanWA]

Run-Time Error '9':

Subscript out of Range

wbMailList.Sheets("Mailinfo").Copy Before:=wbThis.Sheets(2)

This is the first one it chokes on.

 

[Norie]

2 things to check.

1 Is there a worksheet called 'MailInfo' in the workbook wbMailList?

2 Are there 2 workbooks in the workbook the code is in?

For no 2, I based the 2nd part of the copy - the destination - on this code which was in the code in post #1.

Workbooks("Book1").Sheets(2)

You could try this to fix the error.

After:=wbThis.Sheets(wbThis.Sheets.Count)

That will work (I hope, fingers crossed) but it might not copy the sheet into the position you want it to.