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


    Application.ScreenUpdating = False

    Application.DisplayAlerts = False


     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




    'Paste data into new Workbook






      '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.


    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!!!


    • 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()
        Workbooks.Open "S:\macro working folder\ OPS.xlsx"
        ActiveSheet.UsedRange.Copy ThisWorkbook.Worksheets("OPS").Range("A1")
        ActiveWorkbook.Close False
    End Sub

    Wednesday, March 9, 2016 5:23 PM