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
}

 

[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---