Create new workbook and copy data from existing to new workbook.

[Krondorl]

Greetings,

I have a workbook that has 600+ rows of information. I want to iterate through all the rows and copy sections from that sheet to a new workbook that is created on the fly.

The following code is what I am using to attempt this, but the .Range line is giving me a "Run-time error '1004': Application-defined or object-defined error". I have based this code on all the different examples out there.. I am not sure what I am doing wrong..

Dim oXLTG As Object
    Set oXLTG = CreateObject("Excel.Application")
    oXLTG.Visible = True
    oXLTG.DisplayAlerts = 0
    oXLTG.Workbooks.Add
    
    wsMasterList.Activate
    Range(Cells(1, 1), Cells(13, 10)).Copy

    Set wsTransGlobe = oXLTG.Worksheets.Item(1)
    wsTransGlobe.Activate
'    With ActiveSheet
    With oXLTG.Sheets("Sheet1")
        .Columns(1).ColumnWidth = 8
        .Columns(2).ColumnWidth = 28.57
        .Columns(3).ColumnWidth = 85.14
        .Columns(4).ColumnWidth = 9.86
        .Columns(5).ColumnWidth = 14.86
        .Columns(6).ColumnWidth = 3.43
        .Columns(7).ColumnWidth = 6.57
        .Columns(8).ColumnWidth = 3
        .Columns(9).ColumnWidth = 14.14
        .Columns(10).ColumnWidth = 8
        .Range(Cells(1, 1), Cells(13, 10)).Select        <===== Error here
        .Paste
    End With

[AndreRet]

t seems that you have referenced the incorrect object. Try the following -

Dim oXLTG As Object 'Your current code
Dim oXLTG as New Excel.Workbook 'New code.
'Make sure that excell is referenced in 'Project - References'