Copy a sheet from one workbook to another

[cupboy]

Here's my code which does nothing. Generates no error, but doesn't copy the
sheet into the workbook. It does successfully rename the "Operations" sheet,
but is supposed to copy a sheet into the workbook in front of that one.

private void CopyTab(excel.Worksheet ws, excel.Workbook wb)
{
try
{
foreach (excel.Worksheet w in wb.Sheets)
{
if (w.Name.IndexOf("Operations") > -1)
{
w.Copy(ws, Type.Missing); // copy ws into wb workbook
// gets no error, but doesn't do anything else either!
w.Name = "Operations (updated)";
break;
}
} // end foreach
}

 

[gmorris]

Here's my code which does nothing. Generates no error, but doesn't copy
the
sheet into the workbook. It does successfully rename the "Operations"
sheet,
but is supposed to copy a sheet into the workbook in front of that
one.

private void CopyTab(excel.Worksheet ws, excel.Workbook wb)
{
try
{
foreach (excel.Worksheet w in wb.Sheets)
{
if (w.Name.IndexOf("Operations") > -1)
{
w.Copy(ws, Type.Missing); // copy ws into wb workbook
// gets no error, but doesn't do anything else either!
w.Name = "Operations (updated)";
break;
}
} // end foreach
}

I can only assume that this is from a Windows Script file or something,
it looks like JavaScript or C. This forum is for VBA, so you may not get
a lot of help with it here. If you want to do this through Excel's VBA
it will be a lot easier!

 

[ryguy7272]

Try this:
Sub CopySheets()
Application.DisplayAlerts = False
Dim sh As Worksheet
Dim x As Integer

For Each sh In ActiveWorkbook.Worksheets
If InStr(1, sh.Name, "Operations") Then
sh.Select False
Else

For x = 1 To ActiveWorkbook.Sheets.Count - 1
ActiveWorkbook.Sheets(x).Copy _
After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
Next

End If
Next sh
Application.DisplayAlerts = True
End Sub


Make a backup of your data before running any macro of any kind!!

HTH
Ryan---

 

[gmorris]

I'm sure that will work, but what if the OP is wanting to automate this
through Windows Scripting or some kind of addin? If that's the case, you
could still use VBScript (or JavaScript for that matter), but you would
have to create the Excel objects and all that, and he/she still hasn't
said how he/she is trying to accomplish this and may not even know how
to use VBA for all we know...