copy to the last used row of another workbook

[mlov83]

I have a few sheets on 1 workbook and would like to copy all the contents of those sheets onto the last used cells of another workbook. im sure is something small im missing. here is my code. Any help would be greatly appreciated.

i cant get it to run i keep getting a object variable problem.

Dim sht As Variant
Dim wb As Workbook

For Each sht In ActiveWorkbook.Sheets
sht.Activate

wb("ahi12.xls").Sheets("1,2,3").Range("a1:al1").Copy _
Destination:=wb("aui01.xls").sheet("aui01").Range("A1").End(xlUp).Offset(1, 0)




Next sht

 

[PHV]

A starting point:

Dim sht As Variant
Dim wb As Workbook
Set wb = Workbooks("aui01.xls")
For Each sht In ActiveWorkbook.Sheets
    sht.Range("a1:al1").Copy _
    Destination:=wb.Sheets("aui01").Range("A1").End(xlUp).Offset(1, 0)
Next sht

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mlov83]

sht.Range("a1:al1").Copy _
Destination:=wb.Sheets("aui01").Range("A1").End(xlUp).Offset(1, 0)
Next sht

i still keep getting a subscript out of range here? wich i havent been able to figure out. Can anyone help?

 

[firefytr]

Why are you using range A1 and going up? Why not try Range("A" & Rows.Count).End(xlup)....

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[mlov83]

Range("A" & Rows.Count).End(xlup)....

I have actually try that and still get a subscript out of range?... any ideas?

 

[firefytr]

I would adapt PHVs code to something like...

    Dim sht As Worksheet
    Dim wb As Workbook
    Set wb = ActiveWorkbook
    For Each sht In wb.Sheets
        With wb("aui01.xls").Sheet("aui01")
            wb("ahi12.xls").Sheets("1,2,3").Range("a1:al1").Copy _
                Destination:=.Range("A" & .Rows.Count).End(xlUp).Offset(1, 0)
        End With
    Next sht

Is the workbook open?

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[mlov83]

thank you everyone for your help. yes both sheets are opened however now i get the object dosent support this property or method.

Destination:=.Range("A" & .Rows.Count).End(xlUp).Offset(1, 0)

 

[firefytr]

Oops, messed up on my code. Change this line...

        With wb("aui01.xls").Sheet("aui01")

.. to this ..

        With wb.Sheet("aui01")

Also, do you have any chart sheets?

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[mlov83]

With wb.Sheet("aui01")
i still get a object dosesnt support this property error...

 

[xlbo]

Can you use the "Destination" argument of the copy method when not copying to the same workbook ? Doesn;t use the clipboard so may have issues when not in the same object...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[firefytr]

Make sure that sheet name exists. It will work.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[mlov83]

Hello everyone. Thanks for all the replies however i couldn't get my previous code to work but i figured out a different way to get this done. I will posted here in case someone else ever runs into this problem. As XLBO mentioned you cant use "destination" with different workbooks therefore i found an alternative.

'this works and copys all the sheets from one workbook to another.
    Dim WB  As Workbook
    Dim WB1 As Workbook
     
    
    Set WB = Workbooks("aui01.xls") 'change to suit
    Set WB1 = Workbooks("ahi12.xls") 'change to suit
    WB1.Sheets.Copy Before:=WB.Sheets(WB.Sheets.Count)
     
 
    Dim ws As Worksheet
    x = 0
    For Each ws In Worksheets
        If ws.Name <> "aui" Then
            ws.UsedRange.Copy Destination:=Sheets("aui").Range("A" & Rows.Count).End(xlUp)
            x = x + 1
        End If
    Next ws

 

[firefytr]

No, you are completely wrong, as is Geoff, the Destination property WILL work with other workbooks. It must be a valid book/sheet/range though. Again, this works for me...

Sub TestDestinationPropertyOfCopyMethod()
    Dim wb1 As Workbook, wb2 As Workbook, ws2 As Worksheet, ws As Worksheet
    Dim lastRow As Long, lastCol As Long
    Set wb1 = ThisWorkbook 'assumes code is in target workbook
    Set wb2 = Workbooks("DestinationWorkbookNameHere")
    Set ws2 = wb2.Sheets("DestinationWorksheetNameHere")
    For Each ws In wb1.Worksheets
        If ws.Name <> "aui" Then
            lastRow = ws.Cells.Find(what:="*", after:=ws.Cells(1, 1), searchorder:=xlByRows, searchdirection:=xlPrevious).Row
            lastCol = ws.Cells.Find(what:="*", after:=ws.Cells(1, 1), searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
            ws.Range("A1", ws.Cells(lastRow, lastCol)).Copy Destination:=ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Offset(1, 0)
        End If
    Next ws
End Sub

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[xlbo]

sorry for any confusion - I was asking the question myself - not stating it as a fact

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[mlov83]

no problem thank you everyone for all your help on this one.