Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code Error (2003)

    Hi,
    I have altered some code from pasting as special values which worked to just paste, which does'nt??

    Works..

    Set wkbNew = Workbooks.Open(Filename:=strPath & "Bacs In.XLS")
    wkbNew.Worksheets(1).Cells.Copy
    wkbOri.Sheets("Bacs 1").Range("A1").PasteSpecial _
    Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    wkbNew.Close (False)
    wkbOri.Sheets("Bacs 1").Columns("I:I").Select
    Selection.NumberFormat = "#,##0.00"
    Range("A1").Select

    Doesn't work..

    Set wkbNew = Workbooks.Open(Filename:=strPath & "Bacs In.XLS")
    wkbNew.Worksheets(1).Cells.Copy
    wkbOri.Sheets("Bacs 1").Range("A1").Paste
    Application.CutCopyMode = False
    wkbNew.Close (False)
    wkbOri.Sheets("Bacs 1").Columns("I:I").Select
    Selection.NumberFormat = "#,##0.00"
    Range("A1").Select

    [very] <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code Error (2003)

    It would help if you told us in what respect the code doesn't work - do you get an error message, if so where, etc.

    Unlike PasteSpecial, Paste is not a method of the Range object. Replace the two lines

    wkbNew.Worksheets(1).Cells.Copy
    wkbOri.Sheets("Bacs 1").Range("A1").Paste

    with the single line

    wkbNew.Worksheets(1).Cells.Copy Destination:=wkbOri.Sheets("Bacs 1").Range("A1")

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Error (2003)

    Apologies Hans, The error was in the paste line, I will remember to be more specific in future. Thanks.

    Your solution worked,but now I get a runtime 1004 error in line 5.

    Set wkbNew = Workbooks.Open(Filename:=strPath & "Bacs In.XLS")
    wkbNew.Worksheets(1).Cells.Copy Destination:=wkbOri.Sheets("Bacs 1").Range("A1")
    Application.CutCopyMode = False
    wkbNew.Close (False)
    wkbOri.Sheets("Bacs 1").Columns("I:I").Select
    Selection.NumberFormat = "#,##0.00"
    Range("A1").Select

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code Error (2003)

    Try replacing the two lines

    wkbOri.Sheets("Bacs 1").Columns("I:I").Select
    Selection.NumberFormat = "#,##0.00"

    with

    wkbOri.Sheets("Bacs 1").Columns(9).NumberFormat = "#,##0.00"

    or with

    wkbOri.Sheets("Bacs 1").Range("I:I").NumberFormat = "#,##0.00"

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Error (2003)

    Thankyou Hans, works perfect. I have another small problem if you would'nt mind. The following code works fine if there are 2 sheets in Bacs In.xls, though that is not always the case. Would it be possible to ignore the second section of this code if there is no second sheet in Bacs In.xls. Presumeably some sort of If then, if not then.....

    Set wkbNew = Workbooks.Open(Filename:=strPath & "Bacs In.XLS")
    wkbNew.Worksheets(1).Cells.Copy Destination:=wkbOri.Sheets("Bacs 1").Range("A1")
    Application.CutCopyMode = False
    wkbNew.Close (False)
    wkbOri.Sheets("Bacs 1").Range("I:I").NumberFormat = "#,##0.00"
    Range("A1").Select

    Set wkbNew = Workbooks.Open(Filename:=strPath & "Bacs In.XLS")
    wkbNew.Worksheets(2).Cells.Copy Destination:=wkbOri.Sheets("Bacs 2").Range("A1")
    Application.CutCopyMode = False
    wkbNew.Close (False)
    wkbOri.Sheets("Bacs 2").Range("I:I").NumberFormat = "#,##0.00"
    Range("A1").Select

    Thanks for your help!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code Error (2003)

    Try this:
    <code>
    Set wkbNew = Workbooks.Open(Filename:=strPath & "Bacs In.XLS")

    wkbNew.Worksheets(1).Cells.Copy Destination:=wkbOri.Sheets("Bacs 1").Range("A1")
    wkbOri.Sheets("Bacs 1").Range("I:I").NumberFormat = "#,##0.00"

    If wkbNew.Worksheets.Count > 1 Then
    wkbNew.Worksheets(2).Cells.Copy Destination:=wkbOri.Sheets("Bacs 2").Range("A1")
    wkbOri.Sheets("Bacs 2").Range("I:I").NumberFormat = "#,##0.00"
    End If

    wkbNew.Close SaveChanges:=False
    </code>
    There's no need to open and close the workbook twice.

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Error (2003)

    Thanks Hans, your code works great. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •