Copy worksheet to another workbook 1

[kiwieur]

Hi Guys,

I am trying to copy a worksheet from one workbook to another with the following code

Private Sub cmdAppend_Click()
    Sheets("Summary").Copy _
    After:=Workbooks("OTIF_2003.xls").Worksheets(Workbooks("OTIF_2003.xls").Worksheets.Count)
    Workbooks("OTIF_2003.xls").Save
    Workbooks("OTIF_2003.xls").Close
    Range("A2").Select
    frmMacros.Hide
End Sub

However I keep getting the error message

Run-time Error 9:Subscript out of range

could anyone help please

Regards

Paul

 

[kiwieur]

It's OK I have worked it out now, I did not have the workbook open i was trying to copy to

Workbooks("OTIF_2003.xls")

I have now changed my code to this and it works OK

Private Sub cmdAppend_Click()
    Workbooks.Open Filename:="OTIF_2003.xls"
    Dim wsSummary As Worksheet
    Set wsSummary = ActiveSheet
    Sheets("Summary").Copy _
    After:=Workbooks("OTIF_2003.xls").Worksheets(Workbooks("OTIF_2003.xls").Worksheets.Count)
    Workbooks("OTIF_2003.xls").Save
    Workbooks("OTIF_2003.xls").Close
    Range("A2").Select
    frmMacros.Hide
End Sub
[/code/

However if someone knows a better way feel free to enlighten me 

Regards

Paul

 

[xlbo]

why set the sheet object up and then not use it?

Private Sub cmdAppend_Click()
    dim wb as workbook
    set wb = Workbooks.Open Filename:="OTIF_2003.xls"
    with wb
       Sheets("Summary").Copy After:=.Worksheets(.Worksheets.Count)
       .Save
       .Close
    end with
    set wb = nothing
    Range("A2").Select
    frmMacros.Hide
End Sub

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

 

[kiwieur]

Hi Geoff,

Thank you very much for the reply and the suggested code, sorry i have not replied earlier but i have been out of circulation for a while.

I do appreciate your help and as always the "guru's" on this site are always happy to help and educate less experienced users such as myself.

I have applied your code to my application and it works beautifully

once again thank you and a "star" is on it's way

Regards

Paul

 

[xlbo]

No probs - happy to help

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