Excel and VB How to copy worksheets 2

[jonx]

I have 2 excel workbooks, and I was curious how I would copy one worksheet of one workbook to another worksheet on the other workbook? This is what I have.. but it takes like 2 minutes to copy everthing over, and it isn't that big. Is there an easier way?

Code:

Dim pathToSheetOne, pathToSheetTwo As String

Dim I, J As Integer

pathToOne = CurDir() & "\one.xls"
pathToTwo = CurDir() & "\two.xls"

Dim oExcel As Excel.Application
Dim obook As Excel.Workbook
Dim osheet As Excel.Worksheet

oExcel = New Excel.Application
obook = oExcel.Workbooks.Open(pathToOne)

osheet = obook.Worksheets(1)

'Open Up Other Workboook
Dim twoExcel As Excel.Application
Dim twoBook As Excel.Workbook
Dim twoSheet As Excel.Worksheet

twoExcel = New Excel.Application
twoBook = twoExcel.Workbooks.Open(pathToTwo)

twoSheet = twoBook.Worksheets(1)

'Copy over using nested for loops
For I = 1 To 10 Step 1
For J = 1 To 1000 Step 1
osheet.Cells(J, I).Value = twoSheet.Cells(J, I).Value
Next J
Next I

 

[BrainB]

Use an Excel macro. The code below copies a worksheet from one workbook to another. Copy/paste into an Excel code module. In Excel etc. you can set the macro recorder to record your actions and get some code to start with.

Generally Copy/Paste is faster than the nested loops method. For more info I suggest you post queries in the "Visual Basic for Applications(Microsoft)" forum.

Sub test()
    Workbooks.Open FileName:="C:\TEMP\Book1.xls"
    Workbooks.Open FileName:="C:\TEMP\Book2.xls"
    Workbooks("Book1.xls").Sheets("Sheet1").Copy _
        Before:=Workbooks("Book2").Sheets(1)
End Sub

Regards
BrianB
Use CupOfCoffee to speed up all windows applications
================================

 

[jonx]

Thanks, But what does that "_Before:=blahblahbalh" do? Its after the copy? Do you know of any online tutorials I could look at? Thhanks again for the help

-Jon

 

[CubeE101]

the underscore (_) continues a command on to the next line (just ignore it) such as:
Workbooks("Book1.xls").Sheets("Sheet1").Copy Before:=Workbooks("Book2").Sheets(1)

And I believe that should copy it before Sheet1 in Book2

this is from Excel VBA help...

Copy method as it applies to the Chart, Charts, Sheets, Worksheet, and Worksheets objects.

Copies the sheet to another location in the workbook.

[b]expression.Copy(Before, After)[/b]

[b]expression[/b]   Required. An expression that returns one of the above objects.

[b]Before[/b]  Optional Variant. The sheet before which the copied sheet will be placed. You cannot specify Before if you specify After.

[b]After[/b]  Optional Variant. The sheet after which the copied sheet will be placed. You cannot specify After if you specify Before.

[i]Remarks[/i]
If you don't specify either Before or After, Microsoft Excel creates a new workbook that contains the copied sheet.

Though I typically do this a little different...

Sub CopySheet()
  Dim xlApp As Excel.Application
  Dim xlFrom As Workbook, xlTo As Workbook
  Dim xlSheet As Sheet1
  
  On Error Resume Next
  Set xlApp = GetObject(, "Excel.Application")
  If Err.Number Then Set xlApp = CreateObject("Excel.Application")
  On Error GoTo 0
  
  With xlApp
    If .Workbooks.Count > 0 Then
      Set xlFrom = .ActiveWorkbook
      xlFrom.ActiveSheet.Copy
      Set xlTo = .ActiveWorkbook
    End If
  End With
End Sub

This will set xlFrom to the current workbook...
Then copy the active sheet into a new workbook and set it to xlTo
So you can still access both documents...

Have Fun, Be Young... Code BASIC
-Josh

http://cubee.topcities.com

PROGRAMMER: Red-eyed, mumbling mammal capable of conversing with inanimate objects.

 

[jonx]

Thanks for the help guys

 

[jonx]

Would there be a way to paste VBA code directly into my VB project? I would like to run a macro within my VB project on an open Excel workbook. The macro is defined already in the workbook, so all I need to do is run it. Is that possible?

Thanks

 

[jonx]

With the code above, I would like to copy one worksheet to another worksheet in another work book

 

[BrainB]

Here is the VB code. Your main problems initially were that you opened up 2 separate versions of Excel and used loops to transfer data. This does a straight sheet copy.

'=====================================================
Sub CopySheet()
    Dim xlApp As Excel.Application
    Dim xlBook1 As Excel.Workbook
    Dim xlBook2 As Excel.Workbook
    Dim MyPath As String
    Dim MyBook1 As String
    Dim MyBook2 As String
    '---------------------------------------
    '- set Excel application
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err.Number Then
        Set xlApp = CreateObject("Excel.Application")
    End If
    On Error GoTo 0
    '------------------------------------------
    '- open workbooks and copy sheets
    MyPath = "C:\TEMP\"
    MyBook1 = "BOOK1.XLS"
    MyBook2 = "BOOK2.XLS"
    With xlApp
        '.Visible = True
        .Workbooks.Open (MyPath & MyBook1)
        .Workbooks.Open (MyPath & MyBook2)
        Set xlBook1 = .Workbooks(MyBook1)
        Set xlBook2 = .Workbooks(MyBook2)
    End With
    xlBook1.Worksheets("Sheet1").Copy _
              Before:=xlBook2.Sheets(1)
    xlBook2.Save    ' save changes Book2
    '-------------------------------------------
    '- close Excel
    xlApp.Workbooks.Close
    Set xlBook1 = Nothing
    Set xlBook2 = Nothing
    xlApp.Quit
    Set xlApp = Nothing
End Sub
'=======================================================

Regards
BrianB
Use CupOfCoffee to speed up all windows applications
================================