Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    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 ?
    Thanks
    farrukh
    Last edited by farrukh; 2012-03-05 at 06:54. Reason: works but took alot of time optimize please

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 481 Times in 458 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:
    rngCopy.Copy
    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

    zeddy
    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
    Posts
    188
    Thanks
    62
    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

    farrukh

Posting Permissions

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