none
CAn anyone offer a VBA lesson to a NOOB?! RRS feed

  • Question

  • Hi,

    I'm looking to pick up some data on Sheet 1 of my Excel spreadsheet and move it to another page, to display it in another format.  I also need to layer it so that every time I run the macro, it doesn't overwrite the previous line.

    I have this (which is probably quite verbose)

    Sub StatementPost()

    Range("g5:g5").Select

    Selection.Copy

    Sheets("Sheet2").Select

    Range("H2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    (X3 for three different values within sheet one being pasted adjacent to H2 on sheet 2)

    Thanks for looking.

    Mac

    

    Tuesday, April 30, 2013 4:34 PM

All replies

  • Sub StatementPost()

    Dim myRow As Long

     myRow = Sheets("Sheet2").Cells(Rows.Count,"H").End(xlUp).Row + 1

    Sheets("Sheet2").Cells(myRow,"H").Value = Worksheets("Sheet1").Range("G5").Value

    'Change the "H" to the destination column(s) and the "G5" to the other cells, like....

    Sheets("Sheet2").Cells(myRow,"I").Value = Worksheets("Sheet1").Range("K3").Value

    Sheets("Sheet2").Cells(myRow,"J").Value = Worksheets("Sheet1").Range("M9").Value

    End Sub

    Tuesday, April 30, 2013 5:46 PM
  • Absolutely wonderful - did exactly what it said on the tin!

    Thanks Bernie - much appreciated.

    (VBA for Dummies beckons!)

    Thursday, May 2, 2013 8:27 AM