asked on

Excel VBA copy and paste between 2 files

HI,

Let's clarify the requirement :

Everytime open database.xls , then drag mouse left button to highlight some columns (every time different column) , then copy highlighted columns into " D:\SUN_NGAI.xls ", then close window of database.xls .

The result show as attached files
So what VB codes should write for database.xls ?

Thx
database.xls
SUN-NGAI.xls

ASKER CERTIFIED SOLUTION

alainbryden

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

candychan611

ASKER

Sub Macro1()
'
' Macro1 Macro
' Ahdee ( 2010/1/8 ý„èÆ
'

'
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Columns("A:A").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("D:D").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Columns("G:G").Select
Selection.Cut
Columns("E:E").Select
ActiveSheet.Paste
Range("A1:F2").Select
Selection.Copy
Application.WindowState = xlMinimized
Range("A6").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Windows("Book1").Activate
ActiveWindow.Close
End Sub

---the marco cannot paste that sorted data to D:\SUN_NGAI.xls , can u amend the code ?
Thx

candychan611

ASKER

Alain,

Pls ignore the above code,could you amend this one: Next step to paste datas to D:\SUN_NGAI.xls CELL A6

Sub Macro2()
'
' Macro2 Macro
'
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Columns("A:A").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("F:F").Select
Selection.Cut
Columns("A:A").Select
ActiveSheet.Paste
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Columns("H:H").Select
Selection.Cut
Columns("E:E").Select
ActiveSheet.Paste
Range("A1:F2").Select
Selection.Copy
End Sub

SOLUTION

alainbryden

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

candychan611

ASKER

After modify this is working for me :

Sub SUN_NGAI()

Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Columns("A:A").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("F:F").Select
Selection.Cut
Columns("A:A").Select
ActiveSheet.Paste
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Columns("H:H").Select
Selection.Cut
Columns("E:E").Select
ActiveSheet.Paste
Range("A1:F53").Select
ActiveWindow.SmallScroll Down:=-66
Selection.Copy

Workbooks.Open ("D:\.\SUN_NGAI.xls")
Range("A6").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False