Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy Paste Special (Office 2000)

    I use this code extensively in most of my procedures. Can any one help me with more affective code

    'Criteria for copy paste special

    row_t = 4 'Top Row
    row_b = Range("A65536").End(xlUp).Row 'Bottom Row
    col_r = 3 'Column most right
    col_l = 3 'Column most left
    formul = "=+RC[-1]+1000000000" 'Formula to

    Call COPY_PASTE_SPECIAL(row_t, row_b, col_r, col_l, formul)

    Public Sub COPY_PASTE_SPECIAL(row_t, row_b, col_r, col_l, formul)

    Range(Cells(row_t, col_l), Cells(row_b, col_r)).FormulaR1C1 = formul
    Range(Cells(row_t, col_l), Cells(row_b, col_r)).Copy
    Range(Cells(row_t, col_l), Cells(row_b, col_r)).PasteSpecial Paste:=xlValues
    Application.CutCopyMode = False

    End Sub

    Thanks

    Mario

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Paste Special (Office 2000)

    What do you define as "more effective"? I think that the following will be a bit faster:

    <pre>Dim oCell As Range
    Dim row_t As Long, row_b As Long, col_r As Long, col_l As Long
    row_t = 4 'Top Row
    row_b = Range("A65536").End(xlUp).Row 'Bottom Row
    col_r = 3 'Column most right
    col_l = 3 'Column most left
    formul = "=+RC[-1]+1000000000" 'Formula to
    For Each oCell In Range(Cells(row_t, col_l), Cells(row_b, col_r))
    oCell.Value = oCell.Offset(0,-1).value + 1000000000
    Next oCell
    </pre>

    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Paste Special (Office 2000)

    Thanks Legare for the reply. Your way will work for a short table but my table normally are between 20 000 and 40 000 lines.

    Please help me with the Dim abc as Range function. Does this mean that I can assign a range say Range("A1:C100" to the abc value?

    Thanks

    Mario

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Paste Special (Office 2000)

    I don't see any reason why my code would not work on that many rows. You might want to add a few more lines of code that will speed up the loop though:

    <pre>Dim oCell As Range
    Dim row_t As Long, row_b As Long, col_r As Long, col_l As Long
    row_t = 4 'Top Row
    row_b = Range("A65536").End(xlUp).Row 'Bottom Row
    col_r = 3 'Column most right
    col_l = 3 'Column most left
    formul = "=+RC[-1]+1000000000" 'Formula to
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculateManual
    For Each oCell In Range(Cells(row_t, col_l), Cells(row_b, col_r))
    oCell.Value = oCell.Offset(0, -1).Value + 1000000000
    Next oCell
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculateAutomatic
    Application.Calculate
    </pre>


    Yes, if you Dim abc as a Range, then you can assign a range to that variable (the range object, not the value of any cell or cells in the range). However, you have to use the Set statement to do the assignment:

    <pre>Dim abc As Range
    Set abc = ActiveSheet.Range("A1:C100")
    </pre>

    Legare Coleman

  5. #5
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Paste Special (Office 2000)

    Perhaps something like:

    Public Sub COPY_PASTE_SPECIAL(row_t, row_b, col_r, col_l, formul)

    Range(Cells(row_t, col_l), Cells(row_b, col_r)).FormulaR1C1 = formul
    Range(Cells(row_t, col_l), Cells(row_b, col_r)).Value = Range(Cells(row_t, col_l), Cells(row_b, col_r)).Value

    End Sub



    By avoiding the copy to clipboard and paste back, and not having to iterate in a loop, it should perform better.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  6. #6
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Paste Special (Office 2000)

    Thanks for all the advise. I've changed my code with your advise and teaching and it works great

    Dim cps As Range 'Range for copy/paste/special
    Dim formul As String 'Formula for copy/paste/special

    formul = "=+RC[-1]+1000000000" 'Formula for copy/paste/special
    cps = Range(Cells(1, 2),Cells((Range("A65536").End(xlUp).Row), 2))
    Call copy_paste_special(formul, cps)


    Sub copy_paste_special(formul, cps)

    cps.FormulaR1C1 = formul
    cps.Copy
    cps.PasteSpecial Paste:=xlValues
    Application.CutCopyMode = False

    End Sub

    Regards

    Mario

  7. #7
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Paste Special (Office 2000)

    With all the input i've settled for

    Dim cps As Range 'Range for copy/paste/special
    Dim formul As String 'Formula for copy/paste/special

    formul = "=+RC[-1]+1000000000" 'Formula for copy/paste/special
    set cps = Range(Cells(1, 2),Cells((Range("A65536").End(xlUp).Row), 2))

    cps.FormulaR1C1 = formul
    cps.value=cps.value

    Thanks for all the help

    Mario

Posting Permissions

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