PasteSpecial, xlPasteValues, Loops, Range RRS feed

  • Question

  • Hey from what I have searched so far this question hasn't been answered in this forum.

    I'm having a lot of trouble with a spreadsheet.

    What I'm trying to do is copy a range of cells and PasteSpecial them as values in a separate range which is offset from the first. After the values have been moved I would like to copy a different range of cells and use paste special across several different ranges using a For...Next... loop.

    I'll try and tackle the first step before I proceed onto the second. I think I'll be able to get the second depending on the answer, or solution, to the first.

    I guess the first thing I need to ask, or confirm is, can you use conventional cell references or ranges with xl functions (methods?) or do I have to use the "A1:B2" or "R1C1:R2C2" range references? If not is there anyway I can force the function to use conventional cell reference?

    ex.) Can I use - .Worksheets("Sheet1").Range(Cells(2 , 4), Cells(5 , 4)).PasteSpecial Operations= xlPasteValues (followed by destination on next line)

    If there is no way of forcing the conventional cell reference to be used with xl methods, or if the conventional cell reference cannot be used with xl methodes how can I tell the program to select a range based off a previous range? In other words, select a range of cells that are x cells across, and y cells below a specified range?

    Can you do a For...Next... loop with PasteSpecial?

    Here is the code I'm trying to use (keep in mind I've tried other code as well with no success) to solve the first problem.

    Sub Worksheet_Change(ByVal Target As Range)
    'This Sub is placed in "Pile Input"
    Dim oldstartdepth As Integer
    Dim newstartdepth As Integer
    Dim newdepth As Integer
    Dim newpiles As Integer
    oldstartdepth = Worksheet("Pile Input").Cells(27, 3)
    newstartdepth = Worksheet("Pile Input").Cells(27, 5)
    newdepth = Worksheet("Pile Input").Cells(29, 5)
    newpiles = Worksheet("Pile Input").Cells(25, 5)
    If Target.Address = "$E$27" Then
      With Worksheets("Pile Blow Counts")
        .Range(.Cells(21 + oldstartdepth, 5), .Cells(20 + newdepth, 4 + newpiles)).Copy
        .Range(.Cells(21 + newstartdepth, 5), .Cells(20 + newdepth + newdepthstart - olddepthstart, 4 + newpiles)).PasteSpecial Operation:=xlPasteValues
      End With
    End If

    In this particular case I can actually use the regular .Copy/.Paste methods to move the cells because the other properties of the cells are blank, but later I will come across the same problem where I need to paste just the cell properties, not the values.

    I've tried .Value.Copy and .Copy.Value but it doesn't seem to work.

    Thanks for anyhelp.




    Thursday, April 29, 2010 6:36 PM

All replies

  • *Bump*

    Is there perhaps a different forum I should be posting this is in, or something I need to be more specific about?

    Sincerely WoehlkeTAD
    Tuesday, May 18, 2010 1:19 PM