Copying Multiple Worksheets Into New Workbook

Question

I have a master workbook where sometimes I need to copy 2 - 3 worksheets from that master book into a new book.  I have gotten close to acheiving this, but my problem is my code is copying each sheet to it's own workbook instead of copying both sheets to a new workbook.

string[] sheetNames;
sheetNames = new string[2]
{
"Sheet1",
"Sheet2",
};
foeach (string sheet in sheetNames)
{
ws = wb.Sheets[sheet];
if (ws != null)
{
((Worksheet)ws).Copy();
}
}

How can I adapt that to copy both Sheet1 and Sheet2 to the same destination workbook?

Answer 1

Hi,

To copy Sheet1 and Sheet2 to another new workbook you can use the below code:

Sheets(Array("Sheet1", "Sheet2")).Copy

you first need to make an array of sheets in for loop and then outside of "for loop" use the above code:

sheets(ArrayOfSheets).Copy

---

Vish Mishra

Answer 2

Hi,

To copy Sheet1 and Sheet2 to another new workbook you can use the below code:

Sheets(Array("Sheet1", "Sheet2")).Copy

you first need to make an array of sheets in for loop and then outside of "for loop" use the above code:

sheets(ArrayOfSheets).Copy

---

Vish Mishra

I am not following you....could you possibly modify my code to show with your examples so I can better understand?

Answer 3

string[] sheetNames;
sheetNames = new string[2]
{
"Sheet1",
"Sheet2",
};

ws = wb.Sheets[sheetnames];
((Worksheet)ws).Copy();

---

Vish Mishra

Answer 4

string[] sheetNames;
sheetNames = new string[2]
{
"Sheet1",
"Sheet2",
};

ws = wb.Sheets[sheetnames];
((Worksheet)ws).Copy();

---

Vish Mishra

When it hits the ((Worksheet)ws.COpy(); line I get the following debug error:

System.InvalidCastException: Unable to cast COMobject of Type 'System._ComObject' to interface type 'Microsoft.Office.Interop.Excel.WOrksheet'.  This operation failed because the QueryInterface call on the COM component for the interface with IID '(000208D8-0000-0000-C000-00000000000046)
 failed to the following error: No such interface supported

Answer 5

In the above code i did not use the .Get_Item to fetch the list of Sheets in your from the whole workbook.

string[] sheetNames;
sheetNames = new string[2]
{
"Sheet1",
"Sheet2",
};
ws =wb.WorkSheets;
((Worksheet)ws).get_Item(sheetNames)).Copy();

---

Vish Mishra

Answer 6

In the above code i did not use the .Get_Item to fetch the list of Sheets in your from the whole workbook.

string[] sheetNames;
sheetNames = new string[2]
{
"Sheet1",
"Sheet2",
};
ws =wb.WorkSheets;
((Worksheet)ws).get_Item(sheetNames)).Copy();

---

Vish Mishra

On your last line of code, I get a compile error of
Microsoft.Office.interop.Excel.Worksheet doesnot contain a definition for 'get_Item' and no extension method 'get_Item' accepting a first argument of type Microsoft.Office.Interop.Excel.Worksheet could be found?

EDIT -- 

If it makes a difference I have ws declared as such

object ws = null;

Answer 7

I got it working, it just took me a slight modification.

I did below

Excel.Application oXL;
oXL.ActiveWorkbook.Worksheets.get_Item(sheetNames).Copy();