none
Copy and Paste Large data in excel using Macro RRS feed

  • Question

  • Copy and Paste Large data.

    I have a question about using Macro to copy and paste large data to another workbookI have data with 6000 rows running up to column AK. Is this a safe approach? I am going to use this copied data to update 10 pivot tables

    Below is the code that I am using.

       Workbooks.Open Filename:= _

            "S:\macro working folder\ OPS.xlsx"

      'clear any thing on clipboard to maximize available memory

       Application.CutCopyMode = False

      ActiveSheet.Select

    Application.ScreenUpdating = False

    Application.DisplayAlerts = False

     Range("A1").Select

     On Error Resume Next

     mylastrow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

     mylastcol = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column

     mylastcell = Cells(mylastrow - 1, mylastcol).Address

     Data_range = "A1:" & mylastcell

     Range(Data_range).Select

     Selection.Copy

     ActiveWindow.Close

    'Paste data into new Workbook

    '------------------------------------------------------------------------------------------

    Sheet("OPS").Select

    Range("A1").Select

    ActiveSheet.Paste

    Selection.AutoFit

      'clear any thing on clipboard to maximize available memory

       Application.CutCopyMode = False

    I noticed that one of the column is forcing the data to look like this but it looks fine when I do it without macro.

    2.01313E+12

    What are my risks of using macro? I don’t want to lose data or change the data.

    Is there some preventive method that I need to add in my macro?

    Please help!!!

    Thank!!!



    • Edited by ellis Wa Wednesday, March 9, 2016 4:43 PM
    Wednesday, March 9, 2016 4:40 PM

All replies

  • That technique is fine - but here is all the code you really need: I have assumed that the sheet "OPS" is in the same workbook as the code...

    Sub TestMacro()
        ThisWorkbook.Worksheets("OPS").Cells.ClearContents
        Workbooks.Open "S:\macro working folder\ OPS.xlsx"
        ActiveSheet.UsedRange.Copy ThisWorkbook.Worksheets("OPS").Range("A1")
        ActiveWorkbook.Close False
        ThisWorkbook.Worksheets("OPS").Cells.EntireColumn.AutoFit
    End Sub


    Wednesday, March 9, 2016 5:23 PM