A working code I'd like to simplify and/or get advice

  • I'll live with this but thought I'd take advantage of people with more knowledge than me. My code copies and pastes several ranges from one sheet to another. I just wondered if there is a better (more efficient) way than this to do that or if there is anything I left out that I should include. Again, it does what I want but I'm trying to learn.

  • Hi SA,

    I would use the code as follows:

    The application.calculation lines can be left out if there are no formula, or not large numbers of formula, in your sheet.

    Depending on what the values are in the cells being copied it may be preferable to use .value2 rather than .value, if there are dates and currency I think .value can cause issues. You can also change to .Text, but I do not think there is any gain.

    Depending on the purpose of what you are doing there may be more efficient ways of achieving the goal which might be why Roy is asking the question, this is just how I would approach your specific code (though there always seems to be a more efficient approach out there:)


  • You could also use something like this:

    1. Sub Copy_sheet_1_to_another()
    2.     Dim CopyRange As Range
    3.     For Each CopyRange In Worksheets("Proposal 1").Range("B17:E22,I17:L22,A24:F24,B47:L49,U20:Z20,U22:Z33").Areas
    4.         CopyRange.Copy Destination:=Worksheets("Proposal 2").Range(CopyRange.Address)
    5.     Next CopyRange
    6. End Sub

    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why