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
candychan611Asked:
Who is Participating?
 
alainbrydenCommented:
Click the "record a macro" button and then repeat your actions. The macro that appears in Module1 should be very close to what you need. Paste it here and let us know what additional functionality you need.

--
Alain
0
 
candychan611Author Commented:
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
0
 
candychan611Author Commented:
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
0
 
alainbrydenCommented:
The macro should be able to record you pasting it into that workbook too. You have to open the workbook, paste the data, and then save and close it again. The macro should capture all those actions. You cannot paste anything into a closed workbook.

You eventually get code that looks a bit like this:

Workbooks.Open("D:\SUN_NGAI.xls")
Workbooks("SUN_NGAI.xls").Sheet1.Range("A6").Paste
Workbooks("SUN_NGAI.xls").Close True

--
Alain
0
 
candychan611Author Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.