none
Pasting values from one sheet to another RRS feed

  • Question

  • Hi, I am trying to copy a range of cells with values from one worksheet(e.g. Sheet1) to another worksheet(e.g.Sheet2) and then transpose the orientation of the values in the other worksheet(Example: the range from Sheet1 are D5:D10 and then when viewed in Sheet2 the range must be from A1:E1). Next, in case another user wants to add another set of values in D5:D10 in Sheet1, the new set of values must be places in A2:E2 in Sheet2, and so on.  

    This is the code I have been using:

    Dim wss As Worksheet
    Dim wst As Worksheet

    Set wss = Worksheets("Index")
    Set wst = Worksheets("Contracts")

      wss.Range("F17:F27").Copy
      wst.Range("A1").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues, Transpose:=True
      wss.Range("F17:F27").ClearContents
      Application.CutCopyMode = False

    The problem with this code is that it just overwrites the previous values that were written in Sheet2.

    Can anyone help me please? Thank you so much for helping out.

     
    Wednesday, February 27, 2013 2:31 AM

All replies

  • .End(xlDown) works, but only if there are no blank cells. It could be you have a blank cell so your End(xlDown) always hits the same cell, not the end of the list you expect. I would add code to make sure column A always has a value ( Not Isempty(wst.Range("A1").End(xlDown)) after the pastespecial. Enter a space if you don't want anything to be visible.

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Wednesday, February 27, 2013 2:51 AM
  • Better to use is from Down to Up like:

    'wst.Range("A1").End(xlDown)
    wst.cells(wst.rows.count,"a").End(xlup)


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Wednesday, February 27, 2013 11:49 AM
    Answerer