Optimising a Column Insert VBA Code: Convert code from .Cells to .Range?

  • Hi!


    I need help in optimizing below Code that first inserts a column in sheet "Data" before/at Column D, then copies values from Rows 4 to 7 of Column C of Sheet "LIVE" and pastes the same on Sheet "DATA" at position Col. D.


    The problem is that the below code inserts one cell at a time, while I would like to convert it to a .Range code for faster run time.
    Requested since the no. of data to be inserted is about 2000, which leads to memory issues for running cell-wise inserts.


    Code
    Sub CopyMyData()
            
    Sheets("DATA").Columns("D:D").Insert Shift:=xlRight, CopyOrigin:=xlFormatFromLeftOrAbove
     
    Sheets("DATA").Cells(4, 4) = Sheets("LIVE").Cells(4, 3)
    Sheets("DATA").Cells(5, 4) = Sheets("LIVE").Cells(5, 3)
    Sheets("DATA").Cells(6, 4) = Sheets("LIVE").Cells(6, 3)
    Sheets("DATA").Cells(7, 4) = Sheets("LIVE").Cells(7, 3)
     
    End Sub



    Thanks

  • Re: Optimising a Column Insert VBA Code: Convert code from .Cells to .Range?


    Try:

    Code
    Sub CopyMyData()
        Sheets("DATA").Columns("D:D").Insert Shift:=xlRight, CopyOrigin:=xlFormatFromLeftOrAbove
        Sheets("LIVE").Range("C4:C7").Copy Sheets("DATA").Range("D4")
    End Sub

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Re: Optimising a Column Insert VBA Code: Convert code from .Cells to .Range?


    Nopes, your code was only copying formula along with cell formatting etc. My original code is just copying values.
    Following Code helped me though:

    Code
    Sub CopyMyData()
        Worksheets("DATA").Columns("D:D").Insert Shift:=xlRight, CopyOrigin:=xlFormatFromLeftOrAbove
        Worksheets("DATA").Range("C4:C7").Value = Worksheets("LIVE").Range("D4:D7").Value
    End Sub


    Thanks again for your prompt replies though.

  • Re: Optimising a Column Insert VBA Code: Convert code from .Cells to .Range?


    Code
    Option Explicit
    
    
    Sub CopyMyData()
        With Sheets("DATA")
            .Columns("D:D").Insert Shift:=xlRight, CopyOrigin:=xlFormatFromLeftOrAbove
            .Range("D4:D7").Value = Sheets("LIVE").Range("C4:C7").Value
        End With
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!