Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jun 2011
    Thanked 0 Times in 0 Posts

    copy data to template not copy the whole range

    Hi All,

    I need some help i have a workbook which contains many sheets when the data is fill in template (Master) the problem i have tried code hangs the worksheet and remain hangs not copying the data to master sheet quickly plz optimize it

    HTML Code:
    sub DataToMaster ()
    Dim wksMaster As Worksheet
        Dim wksSlave As Worksheet
        Dim rngCopy As Range
        Dim lngCol As Long
        Set wksMaster = ThisWorkbook.Worksheets("Master")
        Set wksSlave = ThisWorkbook.Worksheets("FixedSheet")
        lngCol = 3  'column
        Set rngCopy = Application.Intersect(wksSlave.UsedRange, wksSlave.Columns(lngCol))
        If Not rngCopy Is Nothing Then
             rngCopy.Copy Destination:=wksMaster.Range("EV6")
        End If
        Set rngCopy = Nothing
        Set wksMaster = Nothing
        Set wksSlave = Nothing
    end sub
    any idea for this my problem is to copy the used range of the columns to template starting to paste data to row no 6. When i run the code it hangs and show in the bar shows Cell(press escape to cancel) ? and hangs ,after long long time i have seen above code copy the data can any one optimize this code because iam setting different sheets pasting code below n keep changing column,sheet name ?
    Last edited by farrukh; 2012-03-05 at 05:54. Reason: works but took alot of time optimize please

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Newcazzle, UK
    Thanked 466 Times in 444 Posts
    Hi Farrukh

    There is nothing wrong with your code as such, but I suspect that the range you are 'copying' probably includes some formulas (i.e. in column [C] ), which, when you 'copy' them to column [EV] in the [Master] sheet are probably giving Excel a headache.

    Perhaps what you really want to copy is just the 'values' in the source intersection range??
    In which case, instead of using
    rngCopy.Copy Destination:=wksMaster.Range("EV6")

    you should use:
    wksMaster.Range("EV6").PasteSpecial xlValues

    see attached file.

    Also, if your spreadsheet is very large with lots of formulas, you could also turn off calcs before the copy, and then turn them back on after the copy i.e. in VBA use
    Application.Calculation = xlCalculationManual
    ..code here..
    Application.Calculation = xlCalculationAutomatic

    Attached Files Attached Files

  3. The Following User Says Thank You to zeddy For This Useful Post:

    farrukh (2012-03-05)

  4. #3
    2 Star Lounger
    Join Date
    Jun 2011
    Thanked 0 Times in 0 Posts
    Respected Zeddy,

    Yes i need to copy just values i have changed the line as you mentioned it working great with high performance

    So appreciated...

    Thank you


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts