Announcement

Collapse
No announcement yet.

VBA Copy/Paste from Active Cell sheet 1 to next blank cell sheet 2

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA Copy/Paste from Active Cell sheet 1 to next blank cell sheet 2



    Hello, i need a little help with coding a macro for my workbook, am a bit of a novice at this and am still learning all the Functions of VBA.

    I need the VBA code to be able to copy specific data from cells in the selected active row in the one sheet to a specific range of cells in another worksheet without pasting over existing data.

    For example:

    Sheet 1: I select Rows "1, 2 & 3" As Active rows, i want the data from cell "F" of each of these rows to be copied and pasted to Sheet 2 into the Range of cells A42 to A59.
    Now what want to happen is for the data in (sheet 1) Row 1, F to be pasted into (sheet 2) cell A42, then the data from (sheet 1) rows 2, cell F and 3 cell F to be pasted in the next blank cell below.

    Basically what i have is a Goods In booking system of which i want to create a Delivery note for Goods Out, i want to take the data from each active row i select and to put it into the Range of cells shown above, so that each item is listed one below each other descending in column A starting in Cell 42. I have tried to code it myself, and can only get the data to be pasted into individual separate cells rather than pasting the data in the next blank cell below in colum A.

    Here's my try at it:

    Code:
    Sub IMPORT()
    Sheets("Sheet1").Activate
        Rows(ActiveCell.Row).Select
        Cells(ActiveCell.Row, "F").Select
        Sheets("Sheet2").Activate
        Range("A42").Select
        xlLastRow = .Cells.Find("*", .Cells(1), xlFormulas, _
            xlWhole, xlByRows, xlPrevious).Row
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        End Sub
    The code from above ive got from reading these forums and adapting to my workbook, but obviously doesnt work for me.

    Any help appreciated, thanks!

  • #2


    Re: VBA Copy/Paste from Active Cell sheet 1 to next blank cell sheet 2

    Sounds like the problem you are having is finding the last row?
    Code:
    LastRow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row
    .
    ---
    Old Programmers Never Die ... They just lose their bits

    Comment

    Working...
    X