Code Error (2003)

Topic

New Reply

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]

Reply | Quote

Replies

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”)

Reply | Quote

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

Reply | Quote

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”

Reply | Quote

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!

Reply | Quote

Try this:

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

There’s no need to open and close the workbook twice.

Reply | Quote

Thanks Hans, your code works great.

Reply | Quote