CODEWITHSUNDEEP
excelvbacopy
Omran
Omran

Reputation: 559

how to copy columns from one excel file to another using vba

I am very new to Excel and vba.

I have an excel file that contains a button that does the following hen clicked:

it opens a stored file, copy columns A data and then create a new excel file and paste the copied data on it

I have tried the following code but I have a problem in selection.paste line

Dim oExcel As Excel.Application
Dim oWB As Workbook
Set oExcel = New Excel.Application
'Set oWB = oExcel.Workbooks.Open("C:\Users\omran.alhammadi\Desktop\power ana\4EC73A75.WD0.xls")
Workbooks.Open Filename:="C:\Users\omran.alhammadi\Desktop\power ana\4EC73A75.WD0.xls"



Rows("1:9").Select
    Selection.Delete Shift:=xlUp
    Columns("A:A").Select
    Selection.NumberFormat = "hh:mm:ss;@"

    Columns("A:A").Select
    Selection.Copy
'Selection.NumberFormat = "dd/mm/yyyy hh:mm:ss;@"



Set NewBook = Workbooks.Add
    With NewBook
        .Title = "temp"
        .Subject = "tempsub"
        .SaveAs Filename:="Temp.xls"
    End With

    Workbooks("temp.xls").Activate

ActiveSheet.Columns("A:A").Select
 Range("A1").Paste
Application.CutCopyMode = False



    '    NewBook("4EC73A75.WD0.xls").Activate
    '    Sheets("Sheet1").Activate
    '*** Select the destination cell
   ' Range("A1").Select
   ' ActiveSheet.Range("A1").Paste

    Workbooks("4EC73A75.WD0.xls").Close

Upvotes: 0

Views: 4476

Answers (1)

Rachel Hettinger
Rachel Hettinger

Reputation: 8442

The clipboard is cleared when you save a file, thus the Paste method fails.

Upvotes: 1

Related Questions