No way!

[Rafael Alvares Tubero]

Guys,

I don't understand why it is not working.

Workbooks("C:\job.xls").Activate
Sheets("Report").Select
Range("A1:H1").Select
Selection.Copy
Workbooks.Open ("C:\FX.xls")
Sheets.Add.Name = "Exchange"
Range("A1").Select
Selection.Paste
Columns("A:A").ColumnWidth = 16
Columns("B:B").ColumnWidth = 23
Columns("C:C").ColumnWidth = 23
Columns("D:D").ColumnWidth = 23
Columns("E:E").ColumnWidth = 15
Columns("F:F").ColumnWidth = 15
Columns("G:G").ColumnWidth = 24
Columns("H:H").NumberFormat = "m/d/yyyy"

The code above doesn't work. it stops at red line. Even if I change to "Activesheet.paste" it doesn't work.

May you guys help me?

Thanks a lot,

 

[RoryA]

You are clearing the clipboard with the actions you do after the copy. Try:

Dim wbk as Workbook
set wbk = Workbooks.Open ("C:\FX.xls")
wbk.Sheets.Add.Name = "Exchange"
Workbooks("C:\job.xls").Sheets("Report").Range("A1:H1").Copy destination:=wbk.sheets("Exchange")Range("A1")
Columns("A:A").ColumnWidth = 16
Columns("B:D").ColumnWidth = 23
Columns("E:F").ColumnWidth = 15
Columns("G:G").ColumnWidth = 24
Columns("H:H").NumberFormat = "m/d/yyyy"

 

[VoG]

Try

Workbooks.Open ("C:\FX.xls")
Sheets.Add.Name = "Exchange"
Workbooks("job.xls").Sheets("Report").Range("A1:H1").Copy Destination:=Workbooks("FX.xls").Sheets("Exchange").Range("A1")

 

[Rafael Alvares Tubero]

Superb!!! Thank you very much!!!

 

[RSXchin]

Changing sheets erases the clipboard? If you could explain this a bit more. I normally just fiddle with my code until it lets me paste.

This could save me some time in the future.

 

[VoG]

No, it is opening a workbook that clears the clipboard. You need to have both workbooks open before copying and pasting between them.

 

[RSXchin]

Does it erase if I open ANY workbook? I.E. if I want to clear the clipboard, could I open a blank workbook and close it?

 

[VoG]

Much easier to use

Application.CutCopyMode=False