Copy an Existing then Add Excel Sheet to Same Workbook
Copy an Existing then Add Excel Sheet to Same Workbook
(OP)
Hi all,
I'm working on a macro in EB where I've opened a specific workbook in Excel. At some point I want to insert a copy of one of the worksheets into the same workbook. I'll include the code I have so far that makes a copy of the worksheet and adds it, but it adds it to a new workbook. I can't get the code Excel uses for this function to work in EB. I'll include it too.
This is what works so far:
Dim activesheet as object, objExcel As Object
Dim objWorkBook As Object
'ADD NEW WORKSHEET
objWorkbook.WorkSheets("Batch").Copy
ObjWorkbook.Add
The same command according to Excel VBA would be(long way)
:
objWorkbook.WorkSheets("Batch").Select
objWorkbook.Cells.Select
objWorkbook.Selection.Copy
objWorkbook.WorkSheets("Batch").Select
objWorkbook.WorkSheets.Add
Or (cleanest method):
objWorkbook.WorkSheets("Batch").Select
objWorkbook.WorkSheets("Batch").Copy Before:=Sheets(1)
I think the last command with "Copy Before:=Sheets(1)" is the problem for me. I can't get that into a language EB will allow/execute.
Just sign me desparate (as usual!)
I'm working on a macro in EB where I've opened a specific workbook in Excel. At some point I want to insert a copy of one of the worksheets into the same workbook. I'll include the code I have so far that makes a copy of the worksheet and adds it, but it adds it to a new workbook. I can't get the code Excel uses for this function to work in EB. I'll include it too.
This is what works so far:
Dim activesheet as object, objExcel As Object
Dim objWorkBook As Object
'ADD NEW WORKSHEET
objWorkbook.WorkSheets("Batch").Copy
ObjWorkbook.Add
The same command according to Excel VBA would be(long way)
:
objWorkbook.WorkSheets("Batch").Select
objWorkbook.Cells.Select
objWorkbook.Selection.Copy
objWorkbook.WorkSheets("Batch").Select
objWorkbook.WorkSheets.Add
Or (cleanest method):
objWorkbook.WorkSheets("Batch").Select
objWorkbook.WorkSheets("Batch").Copy Before:=Sheets(1)
I think the last command with "Copy Before:=Sheets(1)" is the problem for me. I can't get that into a language EB will allow/execute.
Just sign me desparate (as usual!)
Talk To Other Members
RE: Copy an Existing then Add Excel Sheet to Same Workbook
Hi,
From EB, have you used the CreateObject method to create an Excel Application object? You need an instance of Excel before you can manipulate Excel objects.
CODE
set xl = CreteObject("Excel.Application")
set wb = xl.workbooks.open(yourworkbookpath)
'''''
Skip,
To be safe on the FOURTH,
Don't take a FIFTH on the THIRD
Or you might not come FORTH on the FIFTH
FORTHWITH
RE: Copy an Existing then Add Excel Sheet to Same Workbook
'=============================================================================================
' Start Excel or Get A Reference To An Open Instance Of Excel
On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If objExcel is Nothing Then
Set objExcel = CreateObject("Excel.Application")
If objExcel is Nothing Then
MsgBox ("Could not open Excel.")
Stop
End If
End If
'Path To The Workbook (*.xls File)
Excelpath = "C:\Documents and Settings\s4019\My Documents\batch sheet.xls"
Set objWorkBook = objExcel.Workbooks.Open (ExcelPath)
If objWorkBook Is Nothing Then
MsgBox "Could not open : " & Excelpath
Stop
End If
If Not objExcel.Visible Then objExcel.Visible = true
RE: Copy an Existing then Add Excel Sheet to Same Workbook
CODE
Dim oExcel as Object
Dim oBook as Object
Dim oSheetToCopy as Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.workbooks.open("c:\test.xls")
Set oSheetToCopy = oBook.Worksheets("Sheet1")
oExcel.visible = true
oSheetToCopy.Copy Before:= oSheetToCopy
End Sub
I think I've forgotten this before.
RE: Copy an Existing then Add Excel Sheet to Same Workbook
ExtraBasic Error
Microsoft Excel: Copy method of worksheet class failed
That's been the problem for me all along.
Still would love help!
RE: Copy an Existing then Add Excel Sheet to Same Workbook
I think I've forgotten this before.
RE: Copy an Existing then Add Excel Sheet to Same Workbook
Sub Main
Dim oExcel as Object
Dim oBook as Object
Dim oSheetToCopy as Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.workbooks.open("c:\test.xls")
Set oSheetToCopy = oBook.Worksheets("Sheet1")
oExcel.visible = true
oSheetToCopy.Copy Before:= oSheetToCopy
end sub
Isn't this what you sent me? Maybe there is a library problem?
RE: Copy an Existing then Add Excel Sheet to Same Workbook
I'm not sure why your getting the message your getting. It works fine in Extra or VBA on my end.
I think I've forgotten this before.
RE: Copy an Existing then Add Excel Sheet to Same Workbook
oSheetToCopy.Copy Before:=oSheetToCopy
RE: Copy an Existing then Add Excel Sheet to Same Workbook
I think I've forgotten this before.
RE: Copy an Existing then Add Excel Sheet to Same Workbook
Can someone help me with simply referencing between multiple sheets in a workbook within the same sub or between subs in the same macro? Thanks